Flatten results

Transact-SQL
1

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
4,5,8,1
5,7,null,4

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
4,5;8;1
5,7;;4

2

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

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

Whichever was easier, faster.

This should work, thanks!