Forum Discussion

Copper Contributor

Jun 28, 2021

Solved

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

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

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.