sql - 查询以列出父列中的每个子记录

Question

我需要专家的帮助。我认为我非常了解 SQL，至少到目前为止是这样。我正在使用 SQL Server，需要构建一个查询，列出每个注册成员并在单独的列而不是行中显示他们的每个 guest 的姓名。每个成员可以有 0:N guest 与每个成员相关联。所以，这是我的表:

成员表:memberID (PK), FName, LName, ...

guest 表:GuestID (PK)、FName、LastName、...

事件表:GuestID (PK), MemberID (PK),...

我正在尝试构建这样的查询输出:

成员(member)ID |成员(member)_FName |成员(member)_L姓名 |客人1ID | Guest1_Fname |客人2ID | 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

输出:

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

如果我需要包含其他信息，请告诉我。

提前致谢!

Answer 1

您可以同时实现 UNPIVOT然后是 PIVOT函数来获取结果。 UNPIVOT获取您的列并将数据转换为行，枢轴获取最终结果并将其转换回列:

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

见 SQL Fiddle with Demo

如果您提前知道记录数，则上述方法效果很好，但如果不知道，则您将需要使用动态 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)

见 SQL Fiddle with Demo

两个版本产生相同的结果:

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