Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DB2 SQL How to complete the pivoting of this data.

Posted on 2016-11-25
5
Medium Priority
?
121 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 35

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 35

Accepted Solution

by:
Pawan Kumar earned 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

971 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