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