[Challenge] Pair-wise and ordered assignment of objects from two different lists

Beyond Relational TSQL Challenge 1；不是很了解規則解釋甚麼，瞭解提供的資料和希望產生的結果，就開始動手 Try 啦。

• 資料來源：

-- Bird Table
Code Name
---- -------
1    Pigeon
2    Sparrow
3    Parrot

-- Grain Table
Code Grain
---- ------
1    Wheat
1    Rice
2    Corn
2    Millet

-- Fruit Table
Code Fruit
---- ------
1    Banana
1    Mango
1    Guava
2    Grapes

• 結果

Code Bird    Grain  Fruit
---- ------- ------ ------
1    Pigeon  Rice   Banana
1    Pigeon  Wheat  Guava
1    Pigeon  NULL   Mango
2    Sparrow Corn   Grapes
2    Sparrow Millet NULL
3    Parrot  NULL   NULL

• 規則
1. For a bird with no food basket at all, a single line should be output with the Grain and Fruit columns containing null.
2. The output should be ordered by Code followed by the order in which the Grain/Fruit pairs were extracted from the Grains/Fruits tables.

• 個人解法

;
WITH CTE_Birds
AS
    (
      SELECT 
        Code,
        [Name],
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY [Name]) AS udOrder
      FROM TC1_BIRDS
    )
,
CTE_Grains
AS
    (
      SELECT 
        Code,
        [Grain],
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY [Grain]) AS udOrder
      FROM TC1_Grains 
    )
,
CTE_Fruits
AS
    (
      SELECT 
        Code,
        [Fruit],
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Fruit) AS udOrder
       FROM TC1_Fruits
    )
SELECT 
   T1.Code,
   T2.Name AS Bird,
   T3.Grain,
   T4.Fruit
FROM
    (
        SELECT Code , udOrder
        FROM CTE_Birds
        UNION
        SELECT Code , udOrder
        FROM CTE_Grains
        UNION
        SELECT Code , udOrder
        FROM CTE_Fruits
    ) AS T1 -- T1 為唯一值 Table，可見下圖
JOIN CTE_Birds AS T2 
        ON T1.Code = T2.Code -- Birds Table 只要針對 唯一值 Table 的 Code 欄位 JOIN
LEFT JOIN CTE_Grains AS T3 
        ON T1.Code = T3.Code AND T1.udOrder = T3.udOrder -- Grains Table 要對 唯一值 Table 的 Code 和 udOrder 欄位 JOIN
LEFT JOIN CTE_Fruits AS T4 
        ON T1.Code = T4.Code AND T1.udOrder = T4.udOrder -- Fruits Table 要對 唯一值 Table 的 Code 和 udOrder 欄位 JOIN
ORDER BY T1.Code

• 唯一值 Table

• 值得參考的解法

--Simon_Frank_tsqlchallenge_1_V2.sql
;
WITH g 
AS
    (
      SELECT Code, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Grain) N, Grain
      FROM dbo.TC1_GRAINS
    )
,
f 
AS
    (
      SELECT Code, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Fruit) N, Fruit
      FROM dbo.TC1_FRUITS
    )
,
p 
AS
    (
      SELECT ISNULL(g.Code, f.Code) Code, Grain, Fruit
      FROM g
      FULL OUTER JOIN f ON g.Code = f.Code AND g.N = f.N
    )
SELECT b.Code, b.Name, p.Grain, p.Fruit
FROM dbo.TC1_BIRDS b LEFT JOIN p ON b.Code = p.Code

參考資料：
• TSQL Challenge 1 - Pair-wise and ordered assignment of objects from two different lists 

延伸閱讀
• UNION