SELECT FROM ( SELECT smpl_smplkind, NVL(COUNT(*),0) AS cnt, TO_CHAR(regi_date,'YYYY') AS regi_date FROM tb_smpl_item WHERE diag_req_no IN ( SELECT diag_relt_no --진단결과번호 FROM tb_diag_relt WHERE del_yn = 'N' AND regi_date BETWEEN TO_DATE('2001-01-01', 'YYYY-MM-DD') AND TO_DATE('2019-04-01', 'YYYY-MM-DD') + 1 ) GROUP BY smpl_smplkind,TO_CHAR(regi_date,'YYYY') ) a, ( SELECT code, code_nm FROM sys_code WHERE code_type = 'SMPL_KIND' ) b WHERE a.smpl_smplkind = b.code ORDER BY smpl_smplkind;
검색기간의 시작일과 끝일을 입력한뒤 출력하면
SMPL_SMPLKIND CNT REGI_DATE CODE
SK01 2 2017 SK01
SK01 3 2018 SK01
SK01 1 2019 SK01
SK02 1 2017 SK02
SK02 4 2018 SK02
이런 형태로 출력됩니다
2001 2002 2003 2004 ~ 2017 2018 2019
SK01 0 0 0 0 2 3 1
SK02 0 0 0 0 0 1 4
이런 형식으로 출력하려면 어떻게 sql을 짜면될까요?