Convert rows into columns sql server

Question

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.

When you write "I want output...", what do you mean? Output to screen, to table? — FDavidov, Jul 25 '16 at 11:59
I am looking for select statement which will invert first table i.e. input table into my required format — Aboli Ogale, Jul 25 '16 at 12:00

objectNotFound · Answer 1 · 2016-07-25T12:21:33.693

0

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

The reason why it didn't work is because PIVOT does not work for dynamic number of rows. Meaning the syntax can transpose only a fixed number of Rows as Columns and you need to specify that in the query. It cannot Dynamically auto generate columns on the fly based on the values in your rows.

Edit:

One Solution I found for dynamic pivot (1st result from google search) : https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

It needs dynamic SQL (there is no other option)

edited Jul 25 '16 at 12:21

answered Jul 25 '16 at 12:13

objectNotFound

1,683
2
18
25

Yes I agree with you. Is there any other way to deal with this problem?? – Aboli Ogale Jul 25 '16 at 12:16
@AboliOgale check the edited answer above for a link – objectNotFound Jul 25 '16 at 12:22

gofr1 · Accepted Answer · 2016-07-25T12:31:01.810

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

Thanks a lot :) It is working as per expectations :) – Aboli Ogale Jul 25 '16 at 12:33 — Aboli Ogale, Jul 25 '16 at 12:33

Chalavadi Kishore · Answer 3 · 2016-12-14T07:27:42.553

0

DECLARE @TABLE TABLE 
   (RowNo INT,Col1 VARCHAR(10),Col2 VARCHAR(10)
   ,Col3 VARCHAR(10))      
INSERT INTO @TABLE VALUES 
   (1,'A','B','C')
SELECT Kishore from @Table
Unpivot(Kishore For Value IN (Col1,Col2,Col3)) AS H

edited Dec 14 '16 at 07:27

answered Dec 14 '16 at 05:20

Chalavadi Kishore

21
6

Convert rows into columns sql server

3 Answers3

Linked