I have the below Query. My Expected output would be as below. Please help me make changes to the Query
select
ID,TERM,
case
when TERM like '____1_' then
function_toget_hrs(ID, TERM,sysdate) else null
end fall_hours,
case
when TERM like '____2_' then
function_toget_hrs(ID, TERM,sysdate) else null
end winter_hours
from TABLE_TERM
where ID='12087762'
Expecting one row for each ID. Please help me the ways
CodePudding user response:
Pivoting is what you need:
WITH TABLE_TERM AS
(
SELECT 12087762 AS ID, '202110____1_' AS term, 12 AS func FROM dual UNION ALL
SELECT 12087762 AS ID, '202120____2_' AS term, 16 FROM dual UNION ALL
SELECT 12087762 AS ID, '202140____1_' AS term, 0 FROM dual
)
SELECT *
FROM (SELECT ID
, DECODE(SUBSTR(term,-6),'____1_','fall_hours','winter_hours') AS hrs
, func --function_toget_hrs(ID, TERM,sysdate) for test purposes
FROM TABLE_TERM
WHERE ID = '12087762'
)
PIVOT (SUM(func) FOR hrs IN ('fall_hours','winter_hours'));