SQL Server 2008 字串分割轉列：將逗號分隔字串拆分為多列資料

需求

將 id, id_value 格式資料，把 id_value 以逗號分隔拆分為多列：

-- 原始資料
id  id_value
1   'aa,bb'
2   'aa,bb,cc'
3   'aaa,bbb,ccc'

-- 目標結果
id  id_value
1   'aa'
1   'bb'
2   'aa'
2   'bb'
2   'cc'
3   'aaa'
3   'bbb'
3   'ccc'

解法：利用系統表產生序號 + SUBSTRING/CHARINDEX

CREATE TABLE ta (id int, id_value varchar(30));
INSERT ta SELECT 1,'aa,bb' UNION ALL SELECT 2,'aa,bb,cc' UNION ALL SELECT 3,'aaa,bbb,ccc'; GO

-- 產生 4000 序號暫存表
SELECT TOP 4000 ix = IDENTITY(int,1,1) INTO #tb FROM sys.objects a, sys.objects b; GO

-- 分割查詢
SELECT
    id,
    id_value = SUBSTRING(id_value, ix, CHARINDEX(',', id_value + ',', ix) - ix)
FROM ta AS A, #tb AS B
WHERE SUBSTRING(',' + id_value, ix, 1) = ','; GO

DROP TABLE ta; DROP TABLE #tb; GO

原理解析

1. #tb 提供 1..4000 序號，模擬遞迴位置。
2. SUBSTRING(',' + id_value, ix, 1) = ',' 找出每個欄位的起始位置 (逗號前一字元)。
3. CHARINDEX(',', id_value + ',', ix) 找出下一個逗號位置，相減得長度。
4. SUBSTRING(id_value, ix, 長度) 切出單一欄位值。

現代替代方案 (SQL Server 2016+)

SELECT id, value AS id_value
FROM ta
CROSS APPLY STRING_SPLIT(id_value, ',');  -- 需相容性層級 130+

本文方法適用 SQL Server 2008/2012/2014 等舊版本；新版本請直接使用 STRING_SPLIT。