multiple rows in one row

[heartthrob]

Hey pals

If I have a table called Shows and another table called Reviews and the sample data looks as follows in the tables:

Shows Table
ShowID..............Title
1...............ShowTitle
2...............ShowTitle2

Reviews Table
ShowID..........PubID..........Review
1.................Pub1..............Rev1
1.................Pub2..............Rev2
1.................Pub4..............Rev4

How can I get the data to output on one line per ShowID with the PubID being the column headings and each corresponding Review to go under the appropriate PubID heading? So for example the above would look like this:

ShowID.......Title............Pub1..........Pub2.. ...........Pub3........Pub4
1..........ShowTitle..........Rev1...........Rev2. ..........NULL..........Rev4

Assume for ease that there are only 4 PubIDs so the PubID headings will only have to -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --

[luminous]

CODE:

select Shows.ShowID
, Shows.Title
, max(case when Reviews.PubID = 'Pub1'
then Reviews.Review end) as Pub1
, max(case when Reviews.PubID = 'Pub2'
then Reviews.Review end) as Pub2
, max(case when Reviews.PubID = 'Pub3'
then Reviews.Review end) as Pub3
, max(case when Reviews.PubID = 'Pub4'
then Reviews.Review end) as Pub4
from Shows
inner
join Reviews
on Reviews.ShowID = Shows.ShowID
group
by Shows.ShowID
, Shows.Title -- This message may have been cut off and the rest will only be shown to members. To become a member, click here --