Unable to do dynamic pivoting in SQL Server 2008 R2

Question

I need to do pivoting in SQL Server 2008 R2. I have an EMP table having employee records and then one Child table where related child records are stored.

Now I want to create one record per employee in output. I'm not sure how to place Child1Name, Child2Name, Child3Name .... Child99Name on column side. Below is sample input and output. TIA.

Answer 1

    /*
    My Sql server 2012
    the code below can run at above 2005
    */
    set nocount on
    ;with EmpTable as (
                        select * 
                        from (values(1,'John'),(2,'Tim'),(3,'Steve')) as a(ID,Name)
                      ),
           ChildTable as(
                         select * 
                         from (values(1,1,'Tina'),(2,1,'Mike'),(3,2,'Mona'),(4,3,'Zane'),(5,3,'Diana'),(6,3,'Marry')) as b(ID,EmpID,Name)
                        ),
           sourceData as(
                         select a.Name,b.EmpID,b.Name as ChildName,row_number() over(partition by EmpID order by B.ID,b.Name) as rn
                         from EmpTable as a inner join ChildTable as b on a.ID = b.EmpID

                        )
          select EmpID,Name,[1] as Child1Name,[2] as Child2Name,[3] as Child3Name
          from sourceData as  a pivot (max(ChildName) for rn in([1],[2],[3]))as b
    /*
    EmpID       Name  Child1Name Child2Name Child3Name
    ----------- ----- ---------- ---------- ----------
    1           John  Tina       Mike       NULL
    2           Tim   Mona       NULL       NULL
    3           Steve Zane       Diana      Marry
    */   



/*
Dynamic  pivoting
My Sql server 2012
the code below can run at above 2005
*/
--step 1 :
set nocount on
;with EmpTable as (
                    select * 
                    from (values(1,'John'),(2,'Tim'),(3,'Steve')) as a(ID,Name)
                  ),
       ChildTable as(
                     select * 
                     from (values(1,1,'Tina'),(2,1,'Mike'),(3,2,'Mona'),(4,3,'Zane'),(5,3,'Diana'),(6,3,'Marry')) as b(ID,EmpID,Name)
                    ),
       sourceData as(
                     select a.Name,b.EmpID,b.Name as ChildName,row_number() over(partition by EmpID order by B.ID,b.Name) as rn
                     from EmpTable as a inner join ChildTable as b on a.ID = b.EmpID

                    )
      select * into #a
      from sourceData 

--Step 2: 
      declare @PivotColumn as varchar(5000),@DisplayColumn as varchar(5000),@Sql as nvarchar(4000)
     ;with columnData as ( select distinct rn from #a)
      select  @PivotColumn = isnull(@PivotColumn,'')+quotename(cast(rn as varchar))+',',@DisplayColumn= isnull(@DisplayColumn,'')+quotename(cast(rn as varchar))+' as Child'+cast(rn as varchar)+'Name,'
      from columnData
      set @PivotColumn = left(@PivotColumn,len(@PivotColumn)-1)
      set @DisplayColumn = left(@DisplayColumn,len(@DisplayColumn)-1)
     set @Sql = 'select EmpID,Name,'+@DisplayColumn+' from #a as a pivot(max(ChildName) for rn in ('+@PivotColumn+')) as b'

     exec sp_executesql @sql
/*
EmpID       Name  Child1Name Child2Name Child3Name
----------- ----- ---------- ---------- ----------
1           John  Tina       Mike       NULL
2           Tim   Mona       NULL       NULL
3           Steve Zane       Diana      Marry
*/