select NUM_OFC_CODE,NUM_RO_CODE,
case when TXT_MONTH='JAN' then 1 ELSE 0 end as JAN,
case when TXT_MONTH='FEB' then 1 ELSE 0 end as FEB,
case when TXT_MONTH='MAR' then 1 ELSE 0 end as MAR,
case when TXT_MONTH='APR' then 1 ELSE 0 end as APR,
case when TXT_MONTH='MAY' then 1 ELSE 0 end as MAY,
case when TXT_MONTH='JUN' then 1 ELSE 0 end as JUN,
case when TXT_MONTH='JUL' then 1 ELSE 0 end as JUL,
case when TXT_MONTH='AUG' then 1 ELSE 0 end as AUG,
case when TXT_MONTH='SEP' then 1 ELSE 0 end as SEP,
case when TXT_MONTH='OCT' then 1 ELSE 0 end as OCT,
case when TXT_MONTH='NOV' then 1 ELSE 0 end as NOV,
case when TXT_MONTH='DEC' then 1 ELSE 0 end as DEC
from LEG_OMBUDSMAN_NONMACT where
NUM_YEAR=2019 group by NUM_OFC_CODE,TXT_MONTH,NUM_RO_CODE;
Result is showing as below:-
NUM_OFC_CODE NUM_RO_CODE JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
280400 280000 0 0 0 0 0 0 0 1 0 0
282300 280000 0 0 0 0 0 0 0 1 0 0 0
281600 280000 0 0 0 0 0 0 0 1 0 0 0
280500 280000 0 0 0 0 0 0 1 0 0 0 0
280500 280000 0 0 0 1 0 0 0 0 0 0 0
281800 280000 0 0 0 0 0 0 0 1 0 0 0
282200 280000 0 0 0 0 0 0 0 1 0 0 0
280500 280000 0 0 0 0 1 0 0 0 0 0 0
280500 280000 0 0 0 0 0 1 0 0 0 0 0
280500 280000 0 0 0 0 0 0 0 1 0 0 0
281300 280000 0 0 0 0 0 0 0 1 0 0 0
I want office wise data. If August data is present, Then It should show 1 else 0. Like wise for other months. But in my query Separate row is showing for separate months.
Basically you have to group the data only by NUM_OFC_CODE,NUM_RO_CODE (excluding TXT_MONTH as you don't want a row for each instance of TXT_MONTH) and then use something like NVL(MAX(CASE WHEN TXT_MONTH='JAN' THEN 1 END), 0) as JAN (using a aggregate function to decide wether an entry exists or not) etc.
It's easier with the use of pivot:
-- Just some sampledata:
WITH LEG_OMBUDSMAN_NONMACT(NUM_OFC_CODE, NUM_RO_CODE, NUM_YEAR, TXT_MONTH) AS
(SELECT 1,1,2019, 'JAN' FROM dual union ALL
SELECT 1,1,2019, 'FEB' FROM dual)
-- Here starts the actual query:
SELECT NUM_OFC_CODE, NUM_RO_CODE
, NVL(JAN,0) AS JAN
, NVL(FEB,0) AS FEB
, NVL(MAR,0) AS MAR
, NVL(APR,0) AS APR
, NVL(MAY,0) AS MAY
, NVL(JUN,0) AS JUN
, NVL(JUL,0) AS JUL
, NVL(AUG,0) AS AUG
, NVL(SEP,0) AS SEP
, NVL(OCT,0) AS OCT
, NVL(NOV,0) AS NOV
, NVL(DEC,0) AS DEC
FROM LEG_OMBUDSMAN_NONMACT
pivot (MAX(1) FOR TXT_MONTH IN ('JAN' AS JAN,'FEB' AS FEB,'MAR' as MAR, 'APR' as APR, 'MAY' as MAY, 'JUN' as JUN, 'JUL' as JUL, 'AUG' as AUG, 'SEP' as SEP, 'OCT' as OCT, 'NOV' as NOV, 'DEC' as DEC ))
WHERE NUM_YEAR=2019