[Challenge] 串接多行資料

Beyond Relational TSQL Beginners Challenge 4

This challenge has been seen in one of the SQL Server forums. The challenge is all about merging the data based on certain conditions.

• Sample Data

ID Name
-- ------------------
1  Deepak Kumar Goyal
2  Niladri Biswas
2  Pratik Shaw
3  Sumi Girijan
3  Suresh Beldar
3  Jeeva Baby

The challenge is that if the Id’s are exactly two in number (e.g. count of ID = 2 is exactly two ) then the names should be concatenated with 'OR'. When Ids are more than two (e.g. Id # 3 has a count of 3) , the values should be concatenated with 'AND'

• Expected Results

ID Name
-- ---------------------------------------------
1  Deepak Kumar Goyal
2  Niladri Biswas OR Pratik Shaw
3  Sumi Girijan AND Suresh Beldar AND Jeeva Baby

• Rules
1. The program should run in SQL SERVER 2005+.
2. Column names should respect the desired output shown.
3. There should be exactly 1 space before and after the OR/AND clause.
4. Output should be in the order of Ascending Id.
5. Names should not be repeated even if it is given in the sample input i.e. if X is appearing more than once in the input for id = 10, the desired output will have only one such record.
6. Names cannot be blank e.g. for id = 10 if the Name field is ‘’(blank), the program should ignore that.

• 個人解法

DECLARE @t TABLE(
 ID INT,
 NAME VARCHAR(MAX)
)
INSERT INTO @t (ID,[NAME]) SELECT 1, 'Deepak Kumar Goyal'
INSERT INTO @t (ID,[NAME]) SELECT 2, 'Niladri Biswas'
INSERT INTO @t (ID,[NAME]) SELECT 2, 'Pratik Shaw'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Sumi Girijan'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Suresh Beldar'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Jeeva Baby'

;
WITH CTE AS
(
  SELECT ID , [Name] , COUNT(ID) OVER (PARTITION BY ID) AS counts
  FROM @t
  WHERE LEN([Name]) > 0
)
SELECT 
  F.ID ,
  CASE 
    WHEN CHARINDEX('OR',F.[Name]) > 0 THEN SUBSTRING(F.[Name],5,LEN(F.[Name])-4)
    WHEN CHARINDEX('AND',F.[Name]) > 0 THEN SUBSTRING(F.[Name],6,LEN(F.[Name])-5)
    ELSE F.[Name]
  END AS [Name]
FROM
  (
    SELECT 
      T1.ID ,
      (
        SELECT 
          CASE
            WHEN counts = 2 THEN ' OR '
            WHEN counts > 2 THEN ' AND '
            ELSE ''
          END + T2.[Name]
        FROM CTE AS T2
        WHERE T1.ID = T2.ID
        FOR XML PATH('')
      ) AS [Name]
    FROM CTE AS T1
    GROUP BY T1.ID
  ) AS F
ORDER BY F.ID

FOR XML PATH 來串接字串時，利用 counts 欄位來決定是用 OR 或是 AND 來當連結，串接後再把第一個連結給拿掉。

• 參考 Solution 後的修正

DECLARE @t TABLE(
 ID INT,
 NAME VARCHAR(MAX)
)
INSERT INTO @t (ID,[NAME]) SELECT 1, 'Deepak Kumar Goyal'
INSERT INTO @t (ID,[NAME]) SELECT 2, 'Niladri Biswas'
INSERT INTO @t (ID,[NAME]) SELECT 2, 'Pratik Shaw'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Sumi Girijan'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Suresh Beldar'
INSERT INTO @t (ID,[NAME]) SELECT 3, 'Jeeva Baby'
INSERT INTO @t (ID,[NAME]) SELECT 3, ''
 
SELECT
  T.ID ,
  CASE
    WHEN T.counts = 2 THEN REPLACE(T.[Name],',',' OR ')
    WHEN T.counts > 2 THEN REPLACE(T.[Name],',',' AND ')
    ELSE T.[Name]
  END AS [Name]
FROM
  (
    SELECT
      T1.ID , 
      COUNT(T1.ID) AS counts,
      STUFF
        (
          (
          SELECT ',' + T2.[Name]
          FROM @t AS T2
          WHERE T1.ID = T2.ID
            AND LEN(T2.Name) > 0
          FOR XML PATH('')
          ) ,
          1 ,
          1 ,
          ''
        ) AS [Name]
    FROM @t AS T1
    WHERE LEN(Name) > 0
    GROUP BY T1.ID
  ) AS T

同樣利用 FOR XML PATH 串接，但是先用逗號來當連接，串接完後再根據 counts 來判斷，看連接是要用 AND 或 OR來取代逗號。

參考資料：
• TSQL Beginners Challenge 4 - Concatenating values from multiple rows
• 不錯的 Solution