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
