10thTiger 10thTiger - 2 months ago 16
SQL Question

Double Pivot. No Aggregate then Aggregate

OK So I posed the other day about a pivot and the moderator just pointed me at another question and it wasn't the answer, but I found my own on another site. This has to do with the same SQL but I need to do a double pivot.

This is my code

CREATE TABLE #tempBene
(accountID varchar(8),
beneName1 varchar(30),
beneamount1 decimal(12,0)
)


INSERT INTO #tempBene
(accountID, beneName, beneamount)
VALUES
('1', 'One', 1),
('1', 'OneTwo', 1),
('1', 'OneThree', 1),
('1', 'OneFour', 1),
('1', 'OneFive', 1),
('1', 'OneSix', 1),

('2', 'TwoOne', 2),
('2', 'TwoTwo', 2),
('2', 'TwoThree', 2),

('3', 'ThreeOne', 3),
('3', 'ThreeTwo', 3),
('3', 'ThreeThree', 3),

('4', 'FourOne', 4),
('4', 'FourTwo', 4),

('5', 'FiveOne', 5)

SELECT ACCOUNTID, [1] as BENE1, [2] as Bene2, [3] as Bene3, [4] as Bene4, [5] as Bene5
INTO #TempBene
FROM
(SELECT ACCOUNTID, BeneficiaryName
ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY ACCOUNTID)
AS ROWNUM
FROM #TempAccount
WHERE DELETEFLAG = 'N') a
PIVOT (MAX(BENEFICIARYName) FOR RowNUM IN ([1], [2], [3], [4], [5])) AS pvt


The first pivot above gets around the aggregate and gives me what part of what I need.

I get

ACCOUNTID Bene1 BENE2 BENE3 BENE4 BENE5
1 one onetwo
2 twoone twotwo twothree twofour twofive


What I need, and I can not figure out is how to include an amount with that as I am not doing an aggregate.

I need

AccountID Bene1 BeneAmt1 Bene2 BeneAmount2 ...
1 one 1 onetwo 1
2 onetwo 2 twotwo 2


Sorry if this is simple to some but I am nto able to add in my amounts that go with each record and maintain my pivot.

Thanks

Answer

you can do it by not using PIVOT but using conditional aggregation.

select  a.accountID,
        MAX(CASE WHEN rowNum = 1 THEN beneName END) AS Bene1,
        SUM(CASE WHEN rowNum = 1 THEN beneAmount END) AS BeneAmt1,
        MAX(CASE WHEN rowNum = 2 THEN beneName END) AS Bene2,
        SUM(CASE WHEN rowNum = 2 THEN beneAmount END) AS BeneAmt2,
        MAX(CASE WHEN rowNum = 3 THEN beneName END) AS Bene3,
        SUM(CASE WHEN rowNum = 3 THEN beneAmount END) AS BeneAmt3,
        MAX(CASE WHEN rowNum = 4 THEN beneName END) AS Bene4,
        SUM(CASE WHEN rowNum = 4 THEN beneAmount END) AS BeneAmt4,
        MAX(CASE WHEN rowNum = 5 THEN beneName END) AS Bene5,
        SUM(CASE WHEN rowNum = 5 THEN beneAmount END) AS BeneAmt5,
        MAX(CASE WHEN rowNum = 6 THEN beneName END) AS Bene6,
        SUM(CASE WHEN rowNum = 6 THEN beneAmount END) AS BeneAmt6
FROM    (
            SELECT  *,
                    ROW_NUMBER() OVER (PARTITION BY accountID ORDER BY accountID) AS rowNum
            FROM    #tempBene 
        ) a
GROUP BY a.accountID
Comments