CODEWITHSUNDEEP
sqlsql-serverdatabasefor-xml-path
Dan Walters
Dan Walters

Reputation: 1376

Transpose group of rows into multiple columns

I'm trying to transpose group of rows into multiple columns.

So far i've been able to aggregate a group of rows into a single column using for xml path, but I need to preserve further data into more columns.

CntTyp table (contact type)

| ContactID | CatCode | CatDesc |
|-----------|---------|---------|
| 89        | 26      | OA      |
| 89        | 27      | OA2     |
| 90        | 26      | OA      |
| 91        | 26      | OA      |
| 91        | 1625    | Donor   |
| 91        | 1625    | Player  |

Desired Output

| ContactID | CatCode | CatDesc | CatCode | CatDesc | CatCode | CatDesc |
|-----------|---------|---------|---------|---------|---------|---------|
| 89        | 26      | OA      | 27      | OA2     |         |         |
| 90        | 26      | OA      |         |         |         |         |
| 91        | 26      | OA      | 1625    | Donor   | 234     | Player  |

My Code:

select ContactID, catInfo = 
STUFF((select ','+cast(t1.CatCode as varchar) 
from CntTyp t1  where t.ContactID = t1.ContactID  
for xml path ('')), 1, 1, '')
from CntTyp t 
group by ContactID

My Output

| ContactID | catInfo     |
|-----------|-------------|
| 89        | 26,27       |
| 90        | 26          |
| 91        | 26,1625,234 |

Upvotes: 1

Views: 1034

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

We can try doing a pivot query with the help of ROW_NUMBER:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ContactID ORDER BY CatCode, CatDesc) rn
    FROM CntTyp
)

SELECT
    ContactID,
    MAX(CASE WHEN rn = 1 THEN CatCode END) AS CatCode1,
    MAX(CASE WHEN rn = 1 THEN CatDesc END) AS CatDesc1,
    MAX(CASE WHEN rn = 2 THEN CatCode END) AS CatCode2,
    MAX(CASE WHEN rn = 2 THEN CatDesc END) AS CatDesc2,
    MAX(CASE WHEN rn = 3 THEN CatCode END) AS CatCode3,
    MAX(CASE WHEN rn = 3 THEN CatDesc END) AS CatDesc3
FROM cte
GROUP BY
    ContactID;

Upvotes: 5

Related Questions