SQL 문제 1

다음의 Filter_result 테이블을 이용해서 애래와 같이 데이터를 집계 하세요
 

ORG_ID_NO	CE_SEQ_NO	FILTER_CODE	FILTER_CHK	FILTER_VAL
1018116293	15	FT01	N	0
1018116293	15	FT02	Y	30.21
1018116293	15	FT06	N	0
1018116293	15	FT10	N	0
1018116293	15	FT11	N	0
1018116293	15	FT15	N	0
1018116293	15	FT16	N	0
1058156337	17	FT01	N	0
1058156337	17	FT02	Y	14.96
1058156337	17	FT06	N	0
1058156337	17	FT09	Y	20.04
1058156337	17	FT10	Y	13.44
1058156337	17	FT11	N	0

이미지가 작으니 클릭해서 크게해서 보세요~

답까지 적어놓으면
1. MS-SQL로 했을 경우

SELECT ORG_ID_NO, CE_SEQ_NO, max(fc1) fc1, max(fcc1) fcc1, max(fc2) fc2, max(fcc2) fcc2, max(fc3) fc3, max(fcc3) fcc3,  
 max(fc4) fc4, max(fcc4) fcc4, max(fc5) fc5, max(fcc5) fcc5, max(fc6) fc6, max(fcc6) fcc6,
 max(fc7) fc7, max(fcc7) fcc7
FROM  
(  

 SELECT ORG_ID_NO, CE_SEQ_NO,  
  case RANK when 1 then filter_code else '-' end as  fc1, case RANK when 1 then filter_chk else '-' end as  fcc1 ,
  case RANK when 2 then filter_code else '-' end as  fc2, case RANK when 2 then filter_chk else '-' end as  fcc2,
  case RANK when 3 then filter_code else '-' end as  fc3, case RANK when 3 then filter_chk else '-' end as  fcc3 ,
  case RANK when 4 then filter_code else '-' end as  fc4, case RANK when 4 then filter_chk else '-' end as  fcc4 ,
  case RANK when 5 then filter_code else '-' end as  fc5, case RANK when 5 then filter_chk else '-' end as  fcc5,
  case RANK when 6 then filter_code else '-' end as  fc6, case RANK when 6 then filter_chk else '-' end as  fcc6 ,
  case RANK when 7 then filter_code else '-' end as  fc7, case RANK when 7 then filter_chk else '-' end as  fcc7
 FROM
 (
  SELECT ORG_ID_NO, CE_SEQ_NO, FILTER_CODE, FILTER_CHK, ISNULL(
  (

     SELECT COUNT(*) FROM FILTER_RESULT
     WHERE X.FILTER_CODE >= FILTER_CODE AND ORG_ID_NO=  X.ORG_ID_NO
  ) , 1) AS RANK
  FROM FILTER_RESULT X
  GROUP BY ORG_ID_NO, CE_SEQ_NO, FILTER_CODE, FILTER_CHK

 )Y
)Z  
GROUP BY ORG_ID_NO, CE_SEQ_NO



2. 오라클로 했을 경우

select org_id_no, ce_seq_no, max(fc1) fc1, max(fcc1) fcc1, max(fc2) fc2, max(fcc2) fcc2, max(fc3) fc3, max(fcc3) fcc3,  
    max(fc4) fc4, max(fcc4) fcc4, max(fc5) fc5, max(fcc5) fcc5, max(fc6) fc6, max(fcc6) fcc6,
    max(fc7) fc1, max(fcc7) fcc7
from
(  
 select org_id_no, ce_seq_no,  
     decode(rn,1,filter_code,'-') fc1,
     decode(rn,1,filter_chk,'-') fcc1,
     decode(rn,2,filter_code,'-') fc2,
     decode(rn,2,filter_chk,'-') fcc2,
     decode(rn,3,filter_code,'-') fc3,
     decode(rn,3,filter_chk,'-') fcc3,
     decode(rn,4,filter_code,'-') fc4,
     decode(rn,4,filter_chk,'-') fcc4,
     decode(rn,5,filter_code,'-') fc5,
     decode(rn,5,filter_chk,'-') fcc5,
     decode(rn,6,filter_code,'-') fc6,
     decode(rn,6,filter_chk,'-') fcc6,
     decode(rn,7,filter_code,'-') fc7,
     decode(rn,7,filter_chk,'-') fcc7
 from  
 (  
   select org_id_no, ce_seq_no, filter_code, filter_chk,  
      row_number() over(partition by org_id_no order by to_number(replace(filter_code,'FT',''))) rn
  from filter_result  
 )  
)  
group by org_id_no, ce_seq_no  

이렇다. 이게 맞는 답인지는 아무도 모른다. 더 최적화 시킬수도 있을터이니
답부터 보지말고 문제를 풀어보고 내가 푼 답은 참고용으로만...