Solved

DB2 SQL How to complete the pivoting of this data.

Posted on 2016-11-25
5
53 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 28

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 28

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exporting image to excel from php document 10 28
Help with query 3 25
MS SQL tables, .net application and ETL connundrum 3 55
Please help with the below query - SQL Server 11 18
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…
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

776 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