CODEWITHSUNDEEP
sqlsql-server
JC Borlagdan
JC Borlagdan

Reputation: 3618

Combine two rows in single column with same Id

This is currently how I return values. but I want to combine rows that have the same Id's. I tried using the unpivot but I couldn't get it to work. here's my query:

   SELECT * FROM 
(
    SELECT 
            ID,
            Setup, 
            Message
    FROM myViewHere
)
AS tempTable
UNPIVOT
(
    [Message]
    FOR Test
    IN (ID, Setup) 
) AS PVT


This is the result of myViewHere

    |ID | Setup  | Message  |
    |---|--------|----------|
    | 1 | Header | myHeader |
    |---|--------|----------|
    | 1 | Footer | myFooter |

What I want to achieve:

    |ID | Header   | Footer   |
    |---|----------|----------|
    | 1 | myHeader | myFooter |
    |---|----------|----------|

Upvotes: 1

Views: 74

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

you can change your view query to be like below

  SELECT * FROM 
(
    SELECT 
            ID,
            Setup, 
            Message
    FROM myViewHere
)
AS tempTable
UNPIVOT
(
    [Message]
    FOR Test
    IN (ID, Setup) 
) AS UNPVT
PIVOT
(
    MAX([Message]) 
    FOR [Setup] 
    IN ([Header], [Footer])
)PVT

Upvotes: 0

vitalygolub
vitalygolub

Reputation: 735

Typical pivot case https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Assumed table name is "source"

SELECT ID, [Header], [Footer]
FROM source
PIVOT
( max(Message) FOR Setup IN ([Header], [Footer])) p;

See test on sqlfeedle http://sqlfiddle.com/#!6/7635f/7

Upvotes: 2

Related Questions