0

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.

4
  • 2
    Pivot is what you are looking for but your sample table has insufficient data. What tells you that two particular EDTU and EDTP values are related to each other? Commented Jun 3, 2019 at 13:37
  • Col 2 here refers to kind of setting names, Col 3 here refers to the value of each setting name. My actual requirement is I have 4 databases in which starting table is common so are the setting names but the values can be different in each database, so i want to get all of them in a single table and try to find a match to execute code against the result obtained. So before going for each database table, I want to first try it in one database. Requesting your suggestions/help Commented Jun 3, 2019 at 13:41
  • 1
    Thanks @Randi for putting the question in a clear way :) :) Commented Jun 3, 2019 at 13:41
  • @NishantSinku no problem! Welcome to dba.se! Consider taking the tour and reading up on asking minimal, verifyable and complete examples for future questions. Good luck :) Commented Jun 3, 2019 at 14:14

1 Answer 1

1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.