SQLTeam.com | Weblogs | Forums

Flatten results



Given this data:

DECLARE @input TABLE (entryid int, week int, teamid int)

INSERT INTO @input VALUES(4,1,5)
INSERT INTO @input VALUES(4,2,8)
INSERT INTO @input VALUES(4,3,1)
INSERT INTO @input VALUES(5,1,7)
INSERT INTO @input VALUES(5,3,4)

What's the best approach to go about getting this:

entryid, w1,w2,w3

This is just a small example my real data will have 30 weeks of data. Do I need to write a bunch of case statements?

I'm also debating with trying to work with the data in this format if it makes it easier.

entryid, picks


Are you looking for a comma-separated single column result, or for a pivoted result? Assuming the latter

FROM @input
PIVOT (MAX(teamid) FOR week IN ([1],[2],[3]))P


Whichever was easier, faster.

This should work, thanks!