SQl Server 2005 Pivot

Question

I want to basically join these 3 table in a view for a report:

Class Table:

ID  Name
1    N1
2    N2
3    N3

Flags Table:

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

Sessions Table:

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

So my desired view should be something like this:

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

Answer 1

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

Answer 2

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