Is Solution PIVOT?

Question

Is Solution PIVOT?

SSCertifiable

Points: 5887

June 14, 2024 at 1:45 pm

Would appreciate some help. I'm trying to convert the 2-column list which is 2 columns to a flat record for each "MyId" ... the header names on the flat record are for example purposes and not important. I'm going to insert this into a table. The example has a max of 3 PayeeId for any MyId; in the real-life data I'm using there are I believe up to 7. Thanks.

DROP TABLE IF EXISTS #example
CREATE TABLE #example
(MyId varchar(10), PayeeId varchar(10))

INSERT INTO #example
SELECT '10001', 'Doug' UNION ALL
SELECT '10002', 'Doug' UNION ALL
SELECT '10002', 'Sue' UNION ALL
SELECT '10003', 'Doug' UNION ALL
SELECT '10003', 'Sue' UNION ALL
SELECT '10003', 'Jeff' UNION ALL
SELECT '10004', 'Doug' UNION ALL
SELECT '10005', 'Doug' UNION ALL
SELECT '10005', 'Sue' UNION ALL
SELECT '10006', 'Sue' UNION ALL
SELECT '10006', 'Jeff'

SELECT * FROM #example

/*
TEST DATA:

10001Doug
10002Doug
10002Sue
10003Doug
10003Sue
10003Jeff
10004Doug
10005Norm
10005Sue
10006Sue
10006Jeff

DESIRED RESULT:

MyIdPayee1Payee2Payee3
1001Doug
1002DougSue
1003DougSueJeff
1004Doug
1005NormSue
1006SueJeff
*/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101096 More actions · Answer 1

I prefer a cross-tab approach for these types of queries:

;WITH cte_data AS (
    SELECT MyId, PayeeId, ROW_NUMBER() OVER(PARTITION BY MyId ORDER BY PayeeId) AS row_num 
    FROM #example
)
SELECT MyId,
    MAX(CASE WHEN row_num = 1 THEN PayeeId ELSE '' END) AS Payee1,
    MAX(CASE WHEN row_num = 2 THEN PayeeId ELSE '' END) AS Payee2,
    MAX(CASE WHEN row_num = 3 THEN PayeeId ELSE '' END) AS Payee3,
    MAX(CASE WHEN row_num = 4 THEN PayeeId ELSE '' END) AS Payee4,
    MAX(CASE WHEN row_num = 5 THEN PayeeId ELSE '' END) AS Payee5,
    MAX(CASE WHEN row_num = 6 THEN PayeeId ELSE '' END) AS Payee6,
    MAX(CASE WHEN row_num = 7 THEN PayeeId ELSE '' END) AS Payee7
    /*, ... */
FROM cte_data
GROUP BY MyId
ORDER BY MyId

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

As an important sidebar, I don't know why you need to do this but it's generally a really bad idea to denormalize data in such a fashion. Maybe post the problem that seems to require this type of denormalization and let's at least look at it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)