Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

DB2 SQL How to complete the pivoting of this data.

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

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 38

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

580 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