Aboli Ogale Aboli Ogale - 4 months ago 31
SQL Question

Convert rows into columns sql server

I want to convert rows into columns.

Column1 Column2 Column3 Column4 Column5 Column6 Column7
1 2016-07-25 7 3 c1 c11 c111
2 2016-07-26 5 2 c2 c22 c222
3 2016-07-27 1 2 c3 c33 c333
4 2016-07-28 3 1 c4 c44 c444


I want output as follows :

Column1 1 2 3 4
Column2 2016-07-25 2016-07-26 2016-07-27 2016-07-28
Column3 7 5 1 3
Column4 3 2 2 1
Column5 c1 c2 c3 c4
Column6 c11 c22 c33 c44
Column7 c111 c222 c333 c44


I tried to do it using pivot unpivot but did not find proper solution.
First Table (input) can have n number of rows.

Answer

You need to use UNPIVOT, then PIVOT and if columns number is unknown - dynamic SQL:

DECLARE @columns nvarchar(max), 
        @columns_with_convert nvarchar(max),
        @row_numbers nvarchar(max),
        @sql nvarchar(max)

SELECT @columns = STUFF((
SELECT ','+QUOTENAME(name)
FROM sys.columns
WHERE [object_id] = OBJECT_ID('##YourTable')
FOR XML PATH('')),1,1,'')
--Will get you [Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7]

SELECT @columns_with_convert = (
SELECT 'CAST('+QUOTENAME(name)+' as nvarchar(max)) as '+QUOTENAME(name) +','
FROM sys.columns
WHERE [object_id] = OBJECT_ID('##YourTable')
FOR XML PATH(''))
--Will get you CAST([Column1] as nvarchar(max)) as [Column1], ... because all rows must be same datatype.

SELECT @row_numbers = STUFF((
SELECT ','+ QUOTENAME(CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as nvarchar(max)))
FROM ##YourTable
FOR XML PATH('')),1,1,'')
--This will be used when PIVOTing ([1],[2],[3],[4])

SELECT @sql = '
SELECT *
FROM (
    SELECT  RN, 
            [Columns],
            [Values]
    FROM (
        SELECT  '+@columns_with_convert+'
                ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RN
        FROM ##YourTable
        ) as p
    UNPIVOT (
        [Values] FOR [Columns] IN ('+@columns+')
    ) as unpvt
) as s
PIVOT (
    MAX([Values]) FOR RN IN ('+@row_numbers+')
) as pvt
'

EXEC sp_executesql @sql

Output:

Columns 1           2           3           4
Column1 1           2           3           4
Column2 2016-07-25  2016-07-26  2016-07-27  2016-07-28
Column3 7           5           1           3
Column4 3           2           2           1
Column5 c1          c2          c3          c4
Column6 c11         c22         c33         c44
Column7 c111        c222        c333        c444

If you PRINT @sql you will get a text of query:

SELECT *
FROM (
    SELECT  RN, 
            [Columns],
            [Values]
    FROM (
        SELECT  CAST([Column1] as nvarchar(max)) as [Column1],CAST([Column2] as nvarchar(max)) as [Column2],CAST([Column3] as nvarchar(max)) as [Column3],CAST([Column4] as nvarchar(max)) as [Column4],CAST([Column5] as nvarchar(max)) as [Column5],CAST([Column6] as nvarchar(max)) as [Column6],CAST([Column7] as nvarchar(max)) as [Column7],
                ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as RN
        FROM ##YourTable
        ) as p
    UNPIVOT (
        [Values] FOR [Columns] IN ([Column1],[Column2],[Column3],[Column4],[Column5],[Column6],[Column7])
    ) as unpvt
) as s
PIVOT (
    MAX([Values]) FOR RN IN ([1],[2],[3],[4])
) as pvt