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
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