SQL Server: Select a list of records from a table for each record in another table?

Question

I currently have a table of Users, and one of Picks (for a football pool). When getting results I run a query to select the users, then in my code I iterate through that list selecting the multiple picks for each user from the other table.

The Picks table has each pick on a different row, so: GameID, UserID, Pick. There is actually a join with another table with the GameID:Week relationship but I can just change that to an IN query and am not worried about that right now.

This is a bit slow and clunky so I was wondering if I can speed it up by querying the entire dataset in one shot. I am pretty sure I did this somewhere before but a long time ago and I can't find the code I used.

Pseudo-coded it would be something like: SELECT UserID, ( SELECT Multiple GameID, Pick FROM Picks WHERE UserID = @UserID). IE I want the multiple rows of the subquery to be returned as columns in the main query. Would this only really be possible with a single value being selected for the subquery?

Answer 1

Since the OP stated "I want the multiple rows of the subquery to be returned as columns in the main query" I'm thinking they're looking for a crosstab or pivot'ed results by user. Something like this

select u.userid, u.username,
       max(case when gw.game_wk=1 then p.pick else null end) wk1_picks,
       max(case when gw.game_wk=2 then p.pick else null end) wk2_picks,
       max(case when gw.game_wk=3 then p.pick else null end) wk3_picks,
       max(case when gw.game_wk=4 then p.pick else null end) wk4_picks,
       max(case when gw.game_wk=5 then p.pick else null end) wk5_picks,
       max(case when gw.game_wk=6 then p.pick else null end) wk6_picks,
       max(case when gw.game_wk=7 then p.pick else null end) wk7_picks,
       max(case when gw.game_wk=8 then p.pick else null end) wk8_picks,
       max(case when gw.game_wk=9 then p.pick else null end) wk9_picks,
       max(case when gw.game_wk=10 then p.pick else null end) wk10_picks
from users u
     left join picks p on p.userid = u.userid
     left join game_weeks gw on p.gameid=gw.gameid
group by u.userid, u.username;