Make Rows as Column names in SQL based on Column Values

Question

I am trying to convert the rows in my table to column names.

My source table is :

My result/expected table is :

I am trying to make the Col_Name values as header or columns and have to place the values in rows based on row number.

Is this achievable in sql?

Many thanks.

Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/a/10404455/1048425), (and also [906 others](https://stackoverflow.com/search?tab=votes&q=sql%20server%20dynamic%20pivot)) — GarethD, Aug 28 '20 at 12:29
Nope Gareth.I have checked that. My data has row number and my output is purely based on row number apart from pivot. — usr_lal123, Aug 28 '20 at 12:31
the principal is exactly the same, you just need to change column names (i.e. `Category` for `Col_Name`) and not select `Row_number` in the final query — GarethD, Aug 28 '20 at 12:47

Atif · Answer 1 · 2020-08-28T12:27:18.860

1

Make use of Union and Case statement to achieve the same:

SELECT MAX(CASE WHEN COL_NAME = 'C1' THEN [VALUE] ELSE NULL END) AS C1,
        MAX(CASE WHEN COL_NAME = 'C2' THEN [VALUE] ELSE NULL END) AS C2 
        FROM Table_PIVOT
UNION
SELECT MIN(CASE WHEN COL_NAME = 'C1' THEN [VALUE] ELSE NULL END) AS C1,
        MIN(CASE WHEN COL_NAME = 'C2' THEN [VALUE] ELSE NULL END) AS C2 
        FROM Table_PIVOT;

Without using Union:

SELECT   MAX(CASE WHEN COL_NAME = 'C1' THEN [VALUE]  END) AS C1,
        MAX(CASE WHEN COL_NAME = 'C2' THEN [VALUE] END) AS C2 
        FROM Table_PIVOT group by [ROW_NUMBER];

Using Pivot:

select C1, C2 FROM (
        SELECT * FROM (SELECT COL_NAME, row_number, [VALUE] AS VAL FROM Table_PIVOT) T
        PIVOT 
            (max(VAL)
            FOR COL_NAME IN ([C1] , [C2])) AS PT) X;

edited Aug 28 '20 at 12:27

answered Aug 28 '20 at 12:04

Atif

2,011
9
23

Thank you mohd. Just for example,I have given 4 Col_Name values c1..c4, I have around 100 Col_Name values like c1,c2....c100. And I have to make the query dynamic depending on Col_Name values. – usr_lal123 Aug 28 '20 at 12:12
1

Updated my answer without using Union but still you have to hardcode your column name. – Atif Aug 28 '20 at 12:26

score 1 · Accepted Answer · answered Aug 28 '20 at 12:55

If you need the columns dynamically you have to use dynamic SQL:

IF OBJECT_ID(N'tempdb..#temp', 'U') IS NOT NULL
    DROP TABLE #temp;

CREATE TABLE #temp ([Col_Name] CHAR(2) NOT NULL, Col_Order INT NOT NULL, [Row_Number] INT NOT NULL, [Value] VARCHAR(5) NOT NULL);
INSERT #temp([Col_Name], Col_Order, [Row_Number], [Value])
VALUES 
    ('C1', 1, 1, '122'),
    ('C2', 2, 1, '123'),
    ('C3', 3, 1, '134'),
    ('C4', 4, 1, '1423'),
    ('C1', 1, 2, '12'),
    ('C2', 2, 2, '324'),
    ('C3', 3, 2, '124'),
    ('C4', 4, 2, 'ba');

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT ',' + QUOTENAME(t.[Col_Name]) 
                    FROM #temp AS t
                    GROUP BY t.[Col_Name], t.Col_Order
                    ORDER BY t.Col_Order
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'),1,1,'');

set @query = 'SELECT ' + @cols + ' FROM (SELECT [Col_Name], [Row_number], [Value] FROM #temp) x
            PIVOT (MAX([Value]) FOR [Col_Name] IN (' + @cols + ')) p; '


EXECUTE sp_executesql @query;

If Row_Number in your sample data isn't a column, and you actually need to use the ROW_NUMBER() function, then the principal is still exactly the same, but instead of selecting the column Row_Number in your dynamic SQL, you would just use the function:

SET @query = 'SELECT ' + @cols + ' 
            FROM (SELECT [Col_Name], 
                        [Row_number] = ROW_NUMBER() OVER(PARTITION BY Col_Name ORDER BY [Row_number]), 
                        [Value] 
                    FROM #temp) x
            PIVOT (MAX([Value]) FOR [Col_Name] IN (' + @cols + ')) p; ';

Make Rows as Column names in SQL based on Column Values

2 Answers2