2009. 11. 22. 19:19
Brain Trainning/DataBase
FOR XML PATH 문을 활용해서 동일계층의 컬럼값을 한줄로..
with custgroups as
(
select customerid,
(select cast(employeeid as varchar(10)) + ';' as [text()] (의미없음-현혹되면 안됨)
from ( select distinct employeeid from dbo.orders as O
where O.customerid = c.customerid) as D
order by employeeid
for xml path('')) as custemps
from dbo.customers as C
)
select customerid,
case when custemps is null then null else min(customerid) over (partition by custemps) end as grp
from custgroups
order by grp, customerid;
with custgroups as
(
select customerid,
(select cast(employeeid as varchar(10)) + ';' as [text()] (의미없음-현혹되면 안됨)
from ( select distinct employeeid from dbo.orders as O
where O.customerid = c.customerid) as D
order by employeeid
for xml path('')) as custemps
from dbo.customers as C
)
select customerid,
case when custemps is null then null else min(customerid) over (partition by custemps) end as grp
from custgroups
order by grp, customerid;
'Brain Trainning > DataBase' 카테고리의 다른 글
Partition Table 의 구현 (0) | 2009.11.30 |
---|---|
인덱스~ (0) | 2009.11.24 |
SQL_Server_2008_System_Views_Poster (0) | 2009.11.15 |
쿼리 및 시스템 튜닝 자료 (0) | 2009.11.12 |
Sqlserver Host명 변경 (0) | 2009.11.12 |