10thTiger - 3 months ago 21

SQL Question

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
```