T-SQL Query: Flattening out a table

sql-server-2008, t-sql

Solution

Assuming you only ever need to list 5 children, this query will work:

with T as (
    select P.Name as ParentName,
           C.Name as ChildName,
           row_number() over (partition by P.ParentId order by C.ChildId) as N
    from ParentTable P join ChildTable C on P.ParentId = C.ParentId
) 
select ParentName,
    max(case when N = 1 then ChildName else '' end) as '1st-child',
    max(case when N = 2 then ChildName else '' end) as '2nd-child',
    max(case when N = 3 then ChildName else '' end) as '3rd-child',
    max(case when N = 4 then ChildName else '' end) as '4th-child',
    max(case when N = 5 then ChildName else '' end) as '5th-child'
from T
group by ParentName

Problem

I need to build a query to resolve this scenario below: ParentTable: ``` ParentId Name 1 Parent A 2 Parent B ``` ChildTable: ``` ChildId ParentId Name 10 1 Child X 11 1 Child Y 12 1 Child Z 13 2 Child Q ``` Where a single parent can be linked to multiple children. The query then would give the following result: ``` Parent Name 1st-Child 2nd-Child 3rd-Child 4th-Child 5th-Child Parent A Child X Child Y Child Z Parent B Child Q ``` Is this possible in MS SQL 2008?

Original source