How to convert row to column dynamically in SQL Server?

Question

I have a table

name    value1  value2  value3
-------------------------------
abc     100     200     200
abc1    101     201     NULL
abc2    NULL    NULL    200
abc3    NULL    NULL    200
abc4    NULL    NULL    200

Expected result

abc     abc1    abc2   abc3  abc4
-----------------------------------
100     101     NULL    NULL  NULL
200     201     NULL    NULL  NULL
200     NULL    200     200   200

Please suggest how to convert this dynamically using pivot or any other method in T-SQL

Does this answer your question? [Simple way to transpose columns and rows in SQL?](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql) — tukan, Apr 08 '20 at 08:26

score 3 · Answer 1 · answered Apr 08 '20 at 08:29

Just make UNPIVOT, then PIVOT:

DROP TABLE dbo.DataSource

CREATE TABLE dbo.DataSource 
(
    [name] VARCHAR(10)
   ,[value1] INT
   ,[value2] INT
   ,[value3] INT
);

INSERT INTO dbo.DataSource ([name], [value1], [value2], [value3])
VALUES ('abc', 100, 200, 200)
      ,('abc1', 101, 201, NULL)
      ,('abc2', NULL, NULL, 200)
      ,('abc3', NULL, NULL, 200)
      ,('abc4', NULL, NULL, 200);

SELECT [abc], [abc1], [abc2], [abc3], [abc4]
FROM dbo.DataSource DS
UNPIVOT
(
    [value] FOR [column] IN ([value1], [value2], [value3])
) UNPVT
PIVOT
(
    MAX([value]) FOR [name] IN ([abc], [abc1], [abc2], [abc3], [abc4])
) PVT;

The dynamic part of this query are the unpivot and the pivot columns. So, if you need a dynamic solution just use the code below:

DECLARE @DynamictSQLStatement NVARCHAR(MAX)
       ,@UnpivotColumns NVARCHAR(MAX)
       ,@PivotColumns NVARCHAR(MAX);

SET @PivotColumns = STUFF
(
    (
        SELECT DISTINCT ',' + QUOTENAME([name])
        FROM dbo.DataSource
        ORDER BY ',' + QUOTENAME([name])
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

SET @UnpivotColumns = STUFF
(
    (
        SELECT DISTINCT ',' + QUOTENAME([name])
        FROM sys.columns
        WHERE [object_id] = OBJECT_ID('dbo.DataSource ')
            AND [name] <> 'name'
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1
    ,1
    ,''
);

SET @DynamictSQLStatement = N'SELECT ' + @PivotColumns + '
FROM dbo.DataSource DS
UNPIVOT
(
    [value] FOR [column] IN (' + @UnpivotColumns + ')
) UNPVT
PIVOT
(
    MAX([value]) FOR [name] IN (' + @PivotColumns + ')
) PVT;'

EXEC sp_executesql @DynamictSQLStatement;

For the UNPIVOT columns we are using system view and for the PIVOT we need to touch the real data.

How to convert row to column dynamically in SQL Server?

1 Answers1