재우니의 블로그

 

 

http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

 

SQL 2005 이상 WITH 함수를 통해 다양하게 JOIN 및 집합물을 처리하여 JOIN 하는 부분이다.

멋진 함수인듯 싶다.

 

WITH
 ProductMaster(Product_ID, ProductName, Price)
 AS
 (
  SELECT 1, ‘XYZ’, 20.10 UNION ALL
  SELECT 2, ‘ABC’, 11.35 UNION ALL
  SELECT 3, ‘PQR’, 05.33
 ),
 Genre(Genre_ID, GenreName)
 AS
 (
  SELECT 101, ‘Horror’ UNION ALL
  SELECT 102, ‘Romantic’ UNION ALL
  SELECT 103, ‘Suspense’
),
Product_Genre(Genre_ID, ProductID)
 AS
 (
  SELECT 101, 1 UNION ALL
  SELECT 102, 1 UNION ALL
  SELECT 101, 2 UNION ALL
  SELECT 101, 3 UNION ALL
  SELECT 103, 3
 ),

cte3 as(
 SELECT
  row_number() over(partition by ProductMaster.Product_ID order by ProductMaster.Product_ID) num,
  Product_ID,
  ProductName,
  Genre.GenreName,
  Price
 FROM
  ProductMaster,
  Genre,
  Product_Genre
 WHERE
 Product_Genre.Genre_ID = Genre.Genre_ID
 AND Product_Genre.ProductID = ProductMaster.Product_ID — )test
 ),

cte4 as
 (
 select * from cte3 where num=1
 ),

cte5 as
 (
 select num,product_id,null as ProductName,GenreName,null as price from cte3 where num1
 )


select
 cte4.Product_ID,
 cte4.ProductName,
 cast(cte4.GenreName as varchar(100)) + (case when cte5.GenreName is null then ” else ‘ | ‘ end)
 + (case when cte5.GenreName is null then ” else cte5.GenreName end) as GenreName,
 cte4.Price
from
 cte4 left outer join cte5 on cte4.Product_ID = cte5.Product_ID