获取一个单亲,所有子级都在一行中

您的示例接近于Pivoting,但我认为Pivot功能在此示例上不可用。

我把你的例子改名为“部门人员”,而不是“儿童家长”,只是为了保持我的理智。

所以,第一个表和一些数据

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 ;

现在,我想扁平化模型,我将使用临时表,因为我有表变量——但是“真实表”上的视图也很好。

/*  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

动态列意味着动态查询,我将把它逐行组合成一个表。

/* 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

现在,将所有查询行连接到一个变量中并执行

/* 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

结果是:

alt text http://www.damirsystems.com/dp_images/morepivot_result_01.png

如果你想换个样子试试这个

样本数据

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

查询

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

产量

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

尝试动态旋转

测试数据

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

查询

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

输出:

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

在看了达米尔·苏达雷维奇的答案之后,我想到了这个问题:(关于桌子结构,请看我的问题)

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