## 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