Convert Rows to column rows in SQL Server

Question

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.

possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — James Z, Jun 25 '15 at 14:02

score 3 · Accepted Answer · answered Jun 25 '15 at 06:23

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)

SQL Fiddle

Convert Rows to column rows in SQL Server

1 Answers1