I am looking for a way to convert the rows in a table to columns rows and assign column names for each.
Players Id Team
john 1 t1
carmel 1 t1
jack 1 t1
james 1 t1
Changed to:
Id Team p1 p2 p3 p4
1 t1 john carmel jack james
There can be any number of players in the above example. I tried using pivot. I could change the rows to columns but not in the fashion I am looking for.
Here is one way using dynamic crosstab. Read this article by Jeff Moden for more details.
DECLARE @sql1 VARCHAR(4000) = ''
DECLARE @sql2 VARCHAR(4000) = ''
DECLARE @sql3 VARCHAR(4000) = ''
SELECT @sql1 =
'SELECT
Id
, Team
'
SELECT @sql2 = @sql2 +
' , MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(10), RN) + ' THEN Players END) AS [' + CONVERT(VARCHAR(10), RN) + ']' + CHAR(10)
FROM(
SELECT DISTINCT
RN = ROW_NUMBER() OVER(PARTITION BY Id, Team ORDER BY (SELECT NULL))
FROM Tbl
)t
ORDER BY RN
SELECT @sql3 =
'FROM(
SELECT *,
RN = ROW_NUMBER() OVER(PARTITION BY Id, Team ORDER BY (SELECT NULL))
FROM Tbl
)t
GROUP BY
Id, Team'
PRINT (@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)
4players? for each team?