Pivot sql

Hi All,

I need help with a sql on how to write a pivot sql for the working sql.

SELECT

DEPTID,JRNL_LINE_SOURCE, SUM(MONETARY_AMOUNT) 'Sum Amount'

FROM PS_JRNL_LN

WHERE BUSINESS_UNIT = 'TEST1'

AND PRODUCT LIKE 'TEST2%'

AND YEAR(JOURNAL_DATE) = '2020'

AND JOURNAL_ID NOT LIKE 'TEST3%' and JRNL_LINE_SOURCE in ('GEX','GAP')

GROUP BY DEPTID, JRNL_LINE_SOURCE having SUM(MONETARY_AMOUNT)>0

ORDER BY 1,2

Dept	Type	Amount
146	GAP	3834.25
391	GAP	11200
391	GEX	374.3

EXPECTED RESULTS on how to write a Pivot SQL, please help me on this.

Dept	GAP	GEX
146	3834.25	0
391	11200	374.3

olafhelper

Jun 28, 2021

rameshdv , an easy way is to use conditional summing, like

select Dept, 
       sum(case when Type = 'GAP' THEN MONETARY_AMOUNT ELSE 0.0 END) AS Gap,
       sum(case when Type = 'GEX' THEN MONETARY_AMOUNT ELSE 0.0 END) AS Gex
from yourTable
where ..
group by Dept

3 Replies

Raksha112

Tin Contributor

May 29, 2023

rameshdv

You can try using this code:

SELECT dept,
  SUM(CASE WHEN JRNL_LINE_SOURCE = 'GAP' THEN monetary_amount ELSE 0 END) AS gap_amount,
  SUM(CASE WHEN JRNL_LINE_SOURCE = 'GEX' THEN monetary_amount ELSE 0 END) AS gex_amount
FROM ps_jrnl_ln
WHERE business_unit = 'TEST1'
  AND product LIKE 'TEST2%'
  AND year(journal_date) = '2020'
  AND journal_id NOT LIKE 'TEST3%'
  AND jrnl_line_source IN ('GEX', 'GAP')
GROUP BY deptid
ORDER BY 1;

I hope this will help.

olafhelper
Bronze Contributor
Jun 28, 2021
rameshdv , an easy way is to use conditional summing, like

select Dept, sum(case when Type = 'GAP' THEN MONETARY_AMOUNT ELSE 0.0 END) AS Gap, sum(case when Type = 'GEX' THEN MONETARY_AMOUNT ELSE 0.0 END) AS Gex from yourTable where .. group by Dept
- rameshdv
  Copper Contributor
  Jun 29, 2021
  Hi olafhelper,
  Thanks a lot for the help.

Forum Discussion

Pivot sql