2개씩 홀수, 짝수로 가로 뽑아서 세로로 세울때

2008.11.20 15:54 개발언어/SQL

2005에서만 가능함


명구선배님 쿼리

with h_gub_rule1CTE (rownum , plusname , scode , sname) AS      
(
 select row_number() over(order by gub_scode) as rownum , a.gub_scode + '@' + b.sd_name as plusname 
 , a.gub_scode as scode , b.sd_name as sname
 from h_gub_rule as a
 left outer join h_sudang as b on a.gub_gubun = b.sd_gubun and a.gub_scode = b.sd_code
 WHERE a.gub_gubun ='01' and a.gub_month ='200811' and {fn substring(a.gub_scode,1,1)} <>'G' and a.gub_check ='1' 
)
, h_gub_rule2CTE (ord, plusname1, scode1, sname1, plusname2, scode2, sname2) AS      
(
 select ceiling ( (rownum+1)/2 ) as ord , 
   case (rownum%2) when 1 then plusname else null end as plusname1 , 
   case (rownum%2) when 1 then scode else null end as scode1 , 
   case (rownum%2) when 1 then sname else null end as sname1 , 
   case (rownum%2) when 0 then plusname else null end as plusname2 ,
   case (rownum%2) when 0 then scode else null end as scode2 , 
   case (rownum%2) when 0 then sname else null end as sname2 
 from h_gub_rule1CTE
)
 
select ord, max(plusname1) as plusname1, max(scode1) as scode1, max(sname1) as sname1,
   max(plusname2) as plusname2, max(scode2) as scode2, max(sname2) as sname2
from h_gub_rule2CTE
group by ord
order by ord



명희선배님 쿼리
--인사에서 공제테이블을 기준으로 해서 만들었구요.(2005에서만 가능해요)

select a.rownum, max(a.gj_code1) as gj_code1 , max(a.gj_name1) as gj_name1
, max(a.gj_code2) as gj_code2, max(a.gj_name2) as gj_name2
from (
 select (a.rank / 2) + (a.rank % 2) as rownum,
 case when a.rank % 2 = 1 then a.gj_code end as gj_code1,
 case when a.rank % 2 = 1 then a.gj_name end as gj_name1,
 case when a.rank % 2 = 0 then a.gj_code end as gj_code2,
 case when a.rank % 2 = 0 then a.gj_name end as gj_name2
 from (
 select rank() OVER (ORDER BY a.gj_code, a.gj_name) as rank, a.gj_code, a.gj_name
    from h_gongje a
 ) a
) a
group by a.rownum


--요건 네이버에서 참조한 지식IN 사이트요.
http://kin.naver.com/detail/detail.php?d1id=1&dir_id=10110&eid=w0eqWYfk3EJtLSxxTCm1qbB+ecDKzPlx&qb=cm93X251bWJlcigptMIgU1FMv6G8rQ==&pid=ffRZXsoi5TlsssEvgEdsss--070250&sid=SST-jwL9JEkAACHcFaI

신고