工作需要筆記

DECLARE @RowData VARCHAR(MAX);
SET @RowData = '1,2,3,;4,5,6,;7,8,9,;';

WITH Num1 (n) AS (SELECT 1 UNION ALL SELECT 1),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Num3 (n) AS (SELECT 1 FROM Num2 AS X, Num2 AS Y),
Num4 (n) AS (SELECT 1 FROM Num3 AS X, Num3 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num4),
SplitRows (row_data, rn) AS (
SELECT SUBSTRING(';' + @RowData + ';', n + 1, 
       CHARINDEX(';', ';' + @RowData + ';', n + 1) - n - 1),
       n + 1 - LEN(REPLACE(LEFT(@RowData, n), ';', ''))
FROM Nums
WHERE SUBSTRING(';' + @RowData + ';', n, 1) = ';'
  AND n < LEN(';' + @RowData + ';')),
SplitColumns (rn, column_data, column_idx) AS (
SELECT rn,
       SUBSTRING(',' + row_data + ',', n + 1, 
       CHARINDEX(',', ',' + row_data + ',', n + 1) - n - 1),
       n + 1 - LEN(REPLACE(LEFT(row_data, n), ',', ''))
FROM Nums
JOIN SplitRows
  ON SUBSTRING(',' + row_data + ',', n, 1) = ','
  AND n < LEN(',' + row_data + ',')),
Pivoted (col1, col2, col3) AS (   
SELECT [1], [2], [3]
FROM SplitColumns AS S
PIVOT 
(MAX(column_data) FOR column_idx IN ([1], [2], [3])) AS P)
SELECT col1, col2, col3
FROM Pivoted
WHERE col1 IS NOT NULL;