Query to list each child record in columns of a parent

Question

I need expert help. I thought I knew SQL pretty well, at least up to this point. I'm working with SQL Server and need to build a query that lists each registered member and show each of the names of their guest(s) in separate columns instead of rows. Each member could have 0:N guests associated with each member. So, Here are my tables:

Member Table: memberID (PK), FName, LName, ...

Guest Table: GuestID (PK), FName, LastName, ...

Event Table: GuestID (PK), MemberID (PK),...

I am trying to build a query output like this:

MemberID | Member_FName | Member_LName | Guest1ID | Guest1_Fname | Guest2ID | Guest2_FName ...

SAMPLE:

Member Table
MemberID | FName | LName
001        Frank   Smith   
002        Mary    Jane
003        John    Henry

Guest Table
GuestID | FName | LName
101       Steve   Smith
102       Peter   Smith
103       Mike    Jane

Event Table
MemberID | GuestID
001        101
001        102
002        103

OUTPUT:

MemberID | FName | LName| GuestID1 | FName1 | LName1 |GuestID2 | FName2 | LName2
001        Frank   Smith  101        Steve    Smith   102        Peter    Smith
002        Mary    Jane   103        Mike     Jane
003        John    Henry

Please let me know if I need to include other information.

thanks in advance!

Answer 1

You can implement both the UNPIVOT and then the PIVOT functions to get the results. The UNPIVOT takes your columns and transforms the data into rows and the pivot takes the final result and turns it back into columns:

select MemberID,
  memberfirst,
  memberlast,
  isNull(GuestId_1, '') GuestId_1, 
  isNull(fname_1, '') fname_1, 
  isNull(lname_1, '') lname_1,
  isNull(GuestId_2, '') GuestId_2, 
  isNull(fname_2, '') fname_2, 
  isNull(lname_2, '') lname_2
from
(
  select MemberID,
    memberfirst,
    memberlast,
    col+'_'+cast(rn as varchar(10)) col,
    value
  from
  (
    select m.MemberID,
      m.fname MemberFirst,
      m.lname MemberLast,
      isNull(cast(g.GuestID as varchar(5)), '') GuestId,
      isNull(g.fname, '') fname,
      isNull(g.lname, '') lname,
      row_number() over(partition by m.parentid order by g.guestid) rn
    from member m
    left join Event r
      on m.parentid = r.memberid
    left join guest g
      on r.guestid = g.guestid
  ) src
  unpivot
  (
    value
    for col in (GuestId, fname, lname)
  ) unpiv
) src1
pivot
(
  max(value)
  for col in (GuestId_1, fname_1, lname_1,
              GuestId_2, fname_2, lname_2)
) piv

See SQL Fiddle with Demo

The above works great if you know the number of records ahead of time, but if not then you will want to use dynamic sql:

DECLARE  @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX),
    @colsPivotNull as  NVARCHAR(MAX)

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name +'_'+ cast(t.rn as varchar(10)))
                    from
                    (
                      select cast(row_number() over(partition by m.MemberID order by g.guestid) as varchar(50)) rn
                      from member m
                      left join Event r
                        on m.parentid = r.memberid
                      left join guest g
                        on r.guestid = g.guestid
                    ) t
                    cross apply sys.columns as C
                   where C.object_id = object_id('guest')
                   group by c.name, t.rn
                   order by t.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colsPivotNull = STUFF((SELECT  ', IsNull(' 
                      + quotename(c.name +'_'+ cast(t.rn as varchar(10)))+', '''') as '+c.name +'_'+ cast(t.rn as varchar(10))
                    from
                    (
                      select cast(row_number() over(partition by m.MemberID order by g.guestid) as varchar(50)) rn
                      from member m
                      left join Event r
                        on m.parentid = r.memberid
                      left join guest g
                        on r.guestid = g.guestid
                    ) t
                    cross apply sys.columns as C
                   where C.object_id = object_id('guest')
                   group by c.name, t.rn
                   order by t.rn
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select 
          MemberID,
          memberfirst,
          memberlast, '+@colsPivotNull+'
      from
      (
        select MemberID,
          memberfirst,
          memberlast,
          col+''_''+cast(rn as varchar(10)) col,
          value
        from 
        (
          select m.MemberID,
            m.fname MemberFirst,
            m.lname MemberLast,
            isNull(cast(g.GuestID as varchar(5)), '''') GuestId,
            isNull(g.fname, '''') fname,
            isNull(g.lname, '''') lname,
            row_number() over(partition by m.parentid order by g.guestid) rn
          from member m
          left join Event r
            on m.parentid = r.memberid
          left join guest g
            on r.guestid = g.guestid
        ) x
        unpivot
        (
         value
          for col in (GuestId, fname, lname)
        ) u
      ) x1
      pivot
      (
        max(value)
        for col in ('+ @colspivot +')
      ) p'

exec(@query)

See SQL Fiddle with Demo

Both versions produce the same result:

| MemberID | MEMBERFIRST | MEMBERLAST | GUESTID_1 | FNAME_1 | LNAME_1 | GUESTID_2 | FNAME_2 | LNAME_2 |
-------------------------------------------------------------------------------------------------------
|        1 |       Frank |      Smith |       101 |   Steve |   Smith |       102 |   Peter |   Smith |
|        2 |        Mary |       Jane |       103 |    Mike |    Jane |           |         |         |
|        3 |        John |      Henry |           |         |         |           |         |         |

Answer 2

It is actually easier to to make one column for the guests and have a comma separated list.

You could do that like this:

SELECT DISTINCT memberID, FName, LName, 
 STUFF((SELECT ', ' + FName + ' ' + LName + ' ('+GuestID+')'
  FROM Relationship R 
  JOIN Guest G ON R.GuestID = G.GuestID
  WHERE R.MemberID = Memeber.MemberID
  FOR XML PATH ('')),1,2,'') AS GuestList
FROM Member

That would look like this:

MemberID | FName | LName| GuestList
001        Frank   Smith  Steve Smith (101), Peter Smith (102)
002        Mary    Jane   Mike Jane (103)
003        John    Henry