Solved

DB2 SQL How to complete the pivoting of this data.

Posted on 2016-11-25
5
41 Views
Last Modified: 2016-11-30
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

0
Comment
Question by:Jeff
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41902048
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
 
LVL 1

Author Comment

by:Jeff
ID: 41906416
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
 
LVL 1

Author Comment

by:Jeff
ID: 41906428
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
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41906670
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
 
LVL 1

Author Closing Comment

by:Jeff
ID: 41907765
Worked Perfectly! Thank you!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now