[Challenge] 計算顏色組合

Beyond Relational TSQL Challenge 44

Here is an interesting challenge which might give you a fun time categorizing products by color. The first part of your job is to identify the unique color combinations of products and the second part is to identify the products that have the given color combination.

• Sample Data

ItemID      Color
----------- ----------
1           Red
1           Green
2           Red
2           Green
2           Yellow
2           Orange
3           Red
4           Green
5           Blue
6           Orange

• Expected Results

ColorCombination        Occurrences Items
----------------------- ----------- ---------
Blue                              1 5
Green                             3 1,2,4
Orange                            2 2,6
Red                               3 1,2,3
Yellow                            1 2
Green+Orange                      1 2
Green+Red                         2 1,2
Green+Yellow                      1 2
Orange+Red                        1 2
Orange+Yellow                     1 2
Red+Yellow                        1 2
Green+Orange+Red                  1 2
Green+Orange+Yellow               1 2
Green+Red+Yellow                  1 2
Orange+Red+Yellow                 1 2
Green+Orange+Red+Yellow           1 2

• Rules
1. The output should be ordered by the number of colors in the color combination column and then by the full color combination name. First all the single color names should come in alphabetical order. This should be followed by all the color combinations that contains two colors, and then three colors etc.
2. The items column should display the values sorted in ascending order.  
3. A color will be associated with an item only once.
4. Your solution should not assume any limits on the number of colors or the number of items other than what can be placed as a list in a varchar(max) column.
5. For a single color combination the list of colors should be in ascending order, i.e. Green+Orange+Red not Green+Red+Orange.
6. Color names will be alphanumeric only.
7. Colors will appear case-insensitive, i.e. if you have 'Orange' you will not have 'ORANGE' or 'orange' or any other variant in the data.

• 個人解法

IF OBJECT_ID('TC44','U') IS NOT NULL
    DROP TABLE TC44
GO
   
CREATE TABLE TC44(
    ItemID INT,
    Color VARCHAR(10) )
      
INSERT INTO TC44 (ItemID, Color)
SELECT 1, 'Red' UNION ALL
SELECT 1, 'Green' UNION ALL
SELECT 2, 'Red' UNION ALL
SELECT 2, 'Green' UNION ALL
SELECT 2, 'Yellow' UNION ALL
SELECT 2, 'Orange' UNION ALL
SELECT 3, 'Red' UNION ALL
SELECT 4, 'Green' UNION ALL
SELECT 5, 'Blue' UNION ALL
SELECT 6, 'Orange'

;
WITH CTE AS
(
  SELECT ItemID , Color , CAST(Color AS varchar(MAX)) AS ColorCombination , 0 AS OrderBy
  FROM TC44
  UNION ALL
  SELECT T1.ItemID , T1.Color , CAST(T2.ColorCombination + '+' + T1.Color AS varchar(MAX)) , OrderBy + 1
  FROM TC44 AS T1
    JOIN CTE AS T2 ON T1.ItemID = T2.ItemID 
                        AND T1.Color > T2.Color
)
SELECT T3.ColorCombination , T3.Occurrences , T4.Items
FROM
  (
    SELECT ColorCombination , OrderBy , COUNT(ColorCombination) AS Occurrences
    FROM CTE 
    GROUP BY ColorCombination , OrderBy  
  ) AS T3
  JOIN
  (
    SELECT ColorCombination, 
      (
        STUFF
          (
            (
              SELECT ',' + CAST(ItemID AS VARCHAR(1))
              FROM CTE T2
              WHERE T2.ColorCombination = T1.ColorCombination
              ORDER BY ItemID
              FOR XML PATH('')
            )
            , 1, 1, '' 
          )
      ) AS Items
    FROM CTE T1
    GROUP BY ColorCombination
  ) AS T4 ON T3.ColorCombination = T4.ColorCombination
ORDER BY T3.OrderBy , T3.ColorCombination

• 個人邏輯：

利用 CTE 把顏色組合跑出來後，再搭配 FOR XML PATH 達成題目要求

參考資料：
• TSQL Challenge 44 - Identify products that belong to each unique color combinations
• Solutions 1、2