I have a select statement which checks for a name and a score from two separated tables. After select operation, I want second scores to be in another column for the same name.
Consider Score 69 will be next to 64 in another column. How can I do that?
Name Score
John 64
Lisa 45
Jack 23
John 69
Requested:
Name Score Score2
John 64 69
For a dynamic number of result columns, you'll almost certainly need to resort to dynamic SQL. Here's a take on this adapted from Bluefeet's answer here
DECLARE
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(CAST(ScoreNum AS VARCHAR))
FROM
(
SELECT Name, Score, ROW_NUMBER() OVER (Partition By Name ORDER BY Score ASC) AS ScoreNum
FROM NameScore
) scores
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')
,1,1,'');
SET @query = 'SELECT Name, ' + @Cols
+ '
FROM
(
SELECT Name, Score, ROW_NUMBER() OVER (Partition By Name ORDER BY Score ASC) AS ScoreNum
FROM NameScore
) scores
PIVOT
(
MIN(Score)
FOR ScoreNum IN (' + @cols + ')
) AS p';
execute(@query);
Noted that we had to run the query twice - once to see how many columns to create, and then again for the actual Pivot. For large amounts of data, you could DRY this up into a #Temp table @TableVar.
