Get a single parent with all children in a single row
Let's asume I have a parent-child structure setup in SQL (server 2005):
CREATE TABLE parent (Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255))
CREATE TABLE child (Id INT IDENTITY PRIMARY KEY, parentId INT, Name VARCHAR(255))
insert into parent select 'parent with 1 child'
insert into parent select 'parent with 2 children'
insert into child(name, parentid) select 'single child of parent 1', 1
insert into child(name, parentid) select 'child 1 of 2 of parent 2', 2
insert into child(name, parentid) select 'child 2 of 2 of parent 2', 2
Is there a way to return one row per parent with it's children as columns? Like:
parent.Id, parent.Name, child(1).Id, child(1).Name, child(2).Id, child(2).Name
Started out with:
select * f开发者_如何学编程rom parent p
left outer join child c1 on c1.parentid = p.id
Your example is close to pivoting, but I do not think that pivot functionality is usable on this one.
I have renamed your example to use "department-person", instead of "child-parent", just to keep my sanity.
So, first tables and some data
DECLARE @Department TABLE
(
DepartmentID int
,DepartmentName varchar(50)
)
DECLARE @Person TABLE
(
PersonID int
,PersonName varchar(50)
,DepartmentID int
)
INSERT INTO @Department
( DepartmentID, DepartmentName )
SELECT 1, 'Accounting' UNION
SELECT 2, 'Engineering' UNION
SELECT 3, 'Sales' UNION
SELECT 4, 'Marketing' ;
INSERT INTO @Person
( PersonID, PersonName, DepartmentID )
SELECT 1, 'Lyne', 1 UNION
SELECT 2, 'Damir', 2 UNION
SELECT 3, 'Sandy', 2 UNION
SELECT 4, 'Steve', 3 UNION
SELECT 5, 'Brian', 3 UNION
SELECT 6, 'Susan', 3 UNION
SELECT 7, 'Joe', 4 ;
Now I want to flatten the model, I'll use temporary table because I have table variables -- but a view on "real tables" would be good too.
/* Create a table with:
DepartmentID, DepartmentName, PersonID, PersonName, PersonListIndex
This could be a view instead of temp table.
*/
IF object_id('tempdb.dbo.#tmpTbl','U') IS NOT NULL
DROP TABLE #tmpTbl
;
WITH prs
AS ( SELECT PersonID
,PersonName
,DepartmentID
,row_number() OVER ( PARTITION BY DepartmentID ORDER BY PersonID ) AS [PersonListIndex]
FROM @Person
),
dptprs
AS ( SELECT d.DepartmentID
,d.DepartmentName
,p.PersonID
,p.PersonName
,p.PersonListIndex
FROM @Department AS d
JOIN prs AS p ON p.DepartmentID = d.DepartmentID
)
SELECT * INTO #tmpTbl FROM dptprs
-- SELECT * FROM #tmpTbl
Dynamic columns means dynamic query, I will compose it row-by-row into a table
/* Table to compose dynamic query */
DECLARE @qw TABLE
(
id int IDENTITY(1, 1)
,txt nvarchar(500)
)
/* Start composing dynamic query */
INSERT INTO @qw ( txt ) VALUES ( 'SELECT' )
INSERT INTO @qw ( txt ) VALUES ( '[DepartmentID]' )
INSERT INTO @qw ( txt ) VALUES ( ',[DepartmentName]' ) ;
/* fetch max number of employees in a department */
DECLARE @i int ,@m int
SET @m = (SELECT max(PersonListIndex) FROM #tmpTbl)
/* Compose dynamic query */
SET @i = 1
WHILE @i <= @m
BEGIN
INSERT INTO @qw ( txt )
SELECT ',MAX(CASE [PersonListIndex] WHEN '
+ cast(@i AS varchar(10)) + ' THEN [PersonID] ELSE NULL END) AS [Person_'
+ cast(@i AS varchar(10)) + '_ID]'
INSERT INTO @qw ( txt )
SELECT ',MAX(CASE [PersonListIndex] WHEN '
+ cast(@i AS varchar(10)) + ' THEN [PersonName] ELSE NULL END) AS [Person_'
+ cast(@i AS varchar(10)) + '_Name]'
SET @i = @i + 1
END
/* Finish the dynamic query */
INSERT INTO @qw (txt) VALUES ( 'FROM #tmpTbl' )
INSERT INTO @qw (txt) VALUES ( 'GROUP BY [DepartmentID], [DepartmentName]' )
INSERT INTO @qw (txt) VALUES ( 'ORDER BY [DepartmentID]' )
-- SELECT * FROM @qw
And now, concatenate all query rows into a variable and execute
/* Create a variable with dynamic sql*/
DECLARE @exe nvarchar(4000)
SET @exe=''
SELECT @exe = @exe + txt + ' ' FROM @qw ORDER BY id
/* execute dynamic sql */
EXEC master..sp_executesql @exe
And here is the result:
If you want a different look try this
Sample data
declare @parent table (Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255))
declare @child table (Id INT IDENTITY PRIMARY KEY, parentId INT, Name VARCHAR(255))
insert into @parent select 'parent with 1 child'
insert into @parent select 'parent with 2 children'
insert into @child(name, parentid) select 'single child of parent 1', 1
insert into @child(name, parentid) select 'child 1 of 2 of parent 2', 2
insert into @child(name, parentid) select 'child 2 of 2 of parent 2', 2
Query
select p.Id as ParentId,p.Name as ParentName
,stuff((select ', ' + 'child(' + cast(c.Id as varchar(10)) + ') : ' + c.Name
from @child c where c.parentId = p.id for xml path('')),1,1,'') 'Child(id):Child Names'
from @parent p
group by
p.Id,p.Name
Output
ParentId ParentName Child(id):Child Names
1 parent with 1 child child(1) : single child of parent 1
2 parent with 2 children child(2) : child 1 of 2 of parent 2, child(3) : child 2 of 2 of parent 2
look at the pivot functionality if you use sql server 2005
Try with dynamic pivoting
Test data
declare @parent table (Id INT IDENTITY PRIMARY KEY, Name VARCHAR(255))
declare @child table (Id INT IDENTITY PRIMARY KEY, parentId INT, Name VARCHAR(255))
insert into @parent select 'parent with 1 child'
insert into @parent select 'parent with 2 children'
insert into @child(name, parentid) select 'single child of parent 1', 1
insert into @child(name, parentid) select 'child 1 of 2 of parent 2', 2
insert into @child(name, parentid) select 'child 2 of 2 of parent 2', 2
Query
declare @col_list varchar(max)
declare @dynquery nvarchar(max)
select
c.Id as ChildId
, p.Id as ParentId
,p.Name as ParentName
,c.Name as ChildName
into #t from @parent p join @child c on p.Id = c.parentId
select @col_list = stuff(cols,1,1,'') from
(select distinct ',[' + cast(ChildName as varchar(50)) + ']'
from #t for xml path(''))X(cols)
set @dynquery = 'select * from #t pivot ( max(ChildId) for ChildName in (' + @col_list + ') ' + ') as pvt'
EXEC master..sp_executesql @dynquery
drop table #t
Output:
ParentId ParentName child 1 of 2 of parent 2 child 2 of 2 of parent 2 single child of parent 1
1 parent with 1 child NULL NULL 1
2 parent with 2 children 2 3 NULL
After looking at Damir Sudarevic's answer I came up with this: (see my question for table structure)
declare @family table (parentid int , parentname varchar(255), child1id int, child1name varchar(255), child2id int, child2name varchar(255))
insert into @family(parentid, parentname)
select id, name from parent
update @family set child1id = c.id, child1name = c.name
from @family f, child c
where c.parentid = f.parentid
update @family set child2id = c.id, child2name = c.name
from @family f, child c
where c.parentid = f.parentid
and not exists
(select child1id from @family where child1id = c.id)
select * from @family
精彩评论