DB2 SQL How to complete the pivoting of this data.

The following query grabs data that I need to pivot but Max and Sum don't play well in the same sentence so to speak. Stubborn Aggregates. I have tried various sub selects but the results come out incredibly wrong.

select hhicusn, hhiclsn,
(CASE HHICRDC WHEN 'DAM' THEN sum(hhiexsg) end) as DAM,
(CASE HHICRDC WHEN 'CON' THEN sum(hhiexsg) end) as CON,
(CASE HHICRDC WHEN 'MIS' THEN sum(hhiexsg) end) as MIS

from pwrdta.hhiorddp

where hhifspd in ( 1, 2, 3) and hhifsyr = 2017 and hhicrin = 'Y' and hhiclsn in ('  1', '  2', '  3', '  4', '  5', '  6', '  7', '  8', '  9', ' 10', ' 12', ' 13', ' 30', ' 31') and hhicrdc in ('DAM', 'CON', 'MIS')

group by hhicusn, hhiclsn, hhicrdc
order by hhicusn, hhiclsn

Open in new window

LVL 1
JeffDeveloperAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Hi Jeff,

Yes you need another select like below..

SELECT hhicusn  ,        hhiclsn , MAX(Dam) Dam ,      MAX(Con)  Con         , MAX(Mis) Mis
FROM
(
select hhicusn, hhiclsn,
SUM(CASE HHICRDC WHEN 'DAM' THEN (hhiexsg) ELSE colName or you can provide hardcoded value also end) as DAM,
SUM(CASE HHICRDC WHEN 'CON' THEN (hhiexsg) ELSE colName or you can provide hardcoded value also end) as CON,
SUM(CASE HHICRDC WHEN 'MIS' THEN (hhiexsg) ELSE colName  or you can provide hardcoded value also end) as MIS

from pwrdta.hhiorddp

where hhifspd in ( 1, 2, 3) and hhifsyr = 2017 and hhicrin = 'Y' and hhiclsn in
('  1', '  2', '  3', '  4', '  5', '  6', '  7', '  8', '  9', ' 10', ' 12', ' 13', ' 30', ' 31')
and hhicrdc in ('DAM', 'CON', 'MIS')

group by hhicusn, hhiclsn, hhicrdc
)k GROUP BY hhicusn  ,        hhiclsn

Hope it helps !!
0
 
Pawan KumarDatabase ExpertCommented:
Could you please post few rows and the expected output. Try...Changes marked in bold.

select hhicusn, hhiclsn,
SUM(CASE HHICRDC WHEN 'DAM' THEN (hhiexsg) ELSE colName or you can provide hardcoded value also end) as DAM,
SUM(CASE HHICRDC WHEN 'CON' THEN (hhiexsg) ELSE colName or you can provide hardcoded value also end) as CON,
SUM(CASE HHICRDC WHEN 'MIS' THEN (hhiexsg) ELSE colName  or you can provide hardcoded value also end) as MIS

from pwrdta.hhiorddp

where hhifspd in ( 1, 2, 3) and hhifsyr = 2017 and hhicrin = 'Y' and hhiclsn in
('  1', '  2', '  3', '  4', '  5', '  6', '  7', '  8', '  9', ' 10', ' 12', ' 13', ' 30', ' 31')
and hhicrdc in ('DAM', 'CON', 'MIS')

group by hhicusn, hhiclsn, hhicrdc
order by hhicusn, hhiclsn
0
 
JeffDeveloperAuthor Commented:
The code above results in the following:

hhicusn          hhiclsn          Dam          Con          Mis
00146             1                     -27.50
00146             1                                       -11.95
00146             1                                                         -15.99

The result I am looking for would be:

hhicusn          hhiclsn          Dam          Con          Mis
00146             1                     -27.50      -11.95       -15.99
0
 
JeffDeveloperAuthor Commented:
I'm guessing I need another select along the lines of

SUM(CASE HHICRDC WHEN 'DAM' THEN (SELECT sum(hhiexsg) from ???

end) as DAM,

I have tried variations, but they keep coming out wrong.
0
 
JeffDeveloperAuthor Commented:
Worked Perfectly! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.