SQL Server 2005 枢轴

Question

我基本上想将这 3 个表加入到报告的视图中：

类表：

ID  Name
1    N1
2    N2
3    N3

标志表：

ID ClassID Flags
1    1      F1
2    1      F2
3    2      F6
4    2      F3
5    3      F2

会话表：

ID  ClassID   Sessions
1      1         S1
2      1         S4
3      2         S3
4      3         S5
5      3         S4

所以我的 期望的视图应该是这样的：

ID  Name    Flags    Sessions
1    N1     F1,F2     S1,S4
2    N2     F6,F3       S3
3    N3      F2       S5,S4

Answer 1

Select C.Id, C.Name
    , Stuff(
            (
            Select ', ' + F1.Flags
            From Flags As F1
            Where F1.ClassId = C.Id
            Order By F1.Flags
            For Xml Path('')
            ), 1, 2, '') As Flags
    , Stuff(
            (
            Select ', ' + S1.Sessions
            From Sessions As S1
            Where S1.ClassId = C.Id
            Order By S1.Flags
            For Xml Path('')
            ), 1, 2, '') As Sessions
From Class As C

Answer 2

select Class.ID,Name,Flags,Sessions
from Class
inner join flags on class.id = flags.classid
inner join sessions on class.id  = sessions.classid