Sql-server – Convert Column values into row values with new row headers

sql serversql-server-2012

Starting table

Col1|col2|col3
---------------
1   |EDTU|Value1
2   |EDTP|Value2
3   |aaaa|value3
4   |bbbb|value4
5   |cccc|value5

I only need values for EDTU and EDTP in below format with new row header

Expected result:

XXXX  |YYYY
----------
value1|value2

Note :- using where clause if col2 = 'EDTU' we get Value1.

Best Answer

Assuming then, that you are going to add (or derive) another column for DatabaseName (or source name) then this should work for you.

DECLARE @T TABLE
    (
    Col1 INT NOT NULL
    , Col2 VARCHAR(10) NOT NULL
    , Col3 VARCHAR(10) NOT NULL
    , DatabaseName VARCHAR(20) NOT NULL
    )

INSERT INTO @T
(Col1, Col2, Col3, DatabaseName)
VALUES (1, 'EDTU', 'Value1', 'FirstDB')
    , (1, 'EDTP', 'Value1', 'FirstDB')
    , (1, 'Other', 'Value1', 'FirstDB')
    , (1, 'EDTU', 'Value5', 'SecondDB')

;WITH CTE_Source AS
    (
    SELECT S.Col2
        , S.Col3
        , S.DatabaseName
    FROM @T AS S
    )
SELECT DatabaseName
    , EDTU
    , EDTP
FROM CTE_Source AS S
    PIVOT (MAX(Col3) FOR Col2 IN (EDTU, EDTP)) AS P
Related Question