SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

拆分分隔符號變成多筆row

google
sql separate string by comma into rows

找到第一筆 stackoverflow
https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows

同篇文章有兩種寫法
一種寫法是在MSSQL 2016之前的寫法

-- a sample temp table
DECLARE @temp_origin TABLE
(
	ID INT
  , input_date DATETIME
  , name VARCHAR(50)
)

INSERT INTO @temp_origin
SELECT 1, '2021-05-05', 'Adam,Gray,Jacy'
 UNION
SELECT 2, '2021-05-08', 'Henry,Alice'
 UNION
SELECT 3, '2021-05-15', 'Ben'

DECLARE @split_character VARCHAR(10)
    SET @split_character = ','

;WITH split_temp(ID, input_date, splitted_name, step_by_step_to_split, origin_concatenated_name) AS
(
    SELECT ID
         , input_date
         , CAST ( LEFT(name, CHARINDEX(@split_character, name + @split_character) - 1) AS VARCHAR(200)) 
		 , STUFF(name, 1, CHARINDEX(@split_character, name + @split_character), '')                     
		 , name
      FROM @temp_origin
	  
	    -- CTE recursion to split name column
     UNION all
    SELECT ID
         , input_date
         , CAST ( LEFT(step_by_step_to_split, CHARINDEX(@split_character, step_by_step_to_split + @split_character) - 1) AS VARCHAR(200))
		 , STUFF(step_by_step_to_split, 1, CHARINDEX(@split_character, step_by_step_to_split + @split_character), '')
		 , origin_concatenated_name
      FROM split_temp
     WHERE step_by_step_to_split > ''
)
SELECT ID 
     , input_date 
     , splitted_name
	 , step_by_step_to_split
	 , origin_concatenated_name
  FROM split_temp
 ORDER BY ID
-- OPTION (maxrecursion 0)

-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

/*
-- for someone who don't understand the CHARINDEX, LEFT, STUFF function work, you can uncomment this block
-- 如果不明白 CHARINDEX, LEFT, STUFF 怎麼運作的，可以把這塊註解拿掉看一下OUTPUT

SELECT ID
         , input_date
		 , CHARINDEX(@split_character, name + @split_character) - 1
         , CAST ( LEFT(name, CHARINDEX(@split_character, name + @split_character) - 1) AS VARCHAR(200))	-- finding the first @split_character and using LEFT to take out the "first string"
		 , CHARINDEX(@split_character, name + @split_character)											  
		 , STUFF(name, 1, CHARINDEX(@split_character, name + @split_character), '')						-- using STUFF to remove the "first string" with @split_character
		 , name
      FROM @temp_origin
*/

-- if you are using the version after MSSQL 2016, you can just using the STRING_SPLIT function
-- 如果你是MSSQL 2016之後的版本，你可以使用 STRING_SPLIT ，在剛剛的同一篇stackoverflow裡面第二個回覆就有說明如何使用

SELECT ID
     , input_date
     , cs.Value --SplitData
  FROM @temp_origin
 CROSS APPLY STRING_SPLIT (name, @split_character) cs

-- 還有 performance 的比較
-- http://sqlperformance.com/2016/03/t-sql-queries/string-split
-- MSSQL 2016之後版本的人就乖乖用 STRING_SPLIT 吧

ID	input_date	splitted_name	step_by_step_to_split	origin_concatenated_name
1	2021-05-05 00:00:00.000	Adam	Gray,Jacy	Adam,Gray,Jacy
1	2021-05-05 00:00:00.000	Gray	Jacy	Adam,Gray,Jacy
1	2021-05-05 00:00:00.000	Jacy		Adam,Gray,Jacy
2	2021-05-08 00:00:00.000	Henry	Alice	Henry,Alice
2	2021-05-08 00:00:00.000	Alice		Henry,Alice
3	2021-05-15 00:00:00.000	Ben		Ben

ID	input_date	Value
1	2021-05-05 00:00:00.000	Adam
1	2021-05-05 00:00:00.000	Gray
1	2021-05-05 00:00:00.000	Jacy
2	2021-05-08 00:00:00.000	Henry
2	2021-05-08 00:00:00.000	Alice
3	2021-05-15 00:00:00.000	Ben