SPLIT LINE USE SQL【REPRODUCED】
QUESTION:
I have a table like this
ID CommaSeperatedString
1 a,b,c,d,e
2 x,y,z
What I want is to convert this into
ID Text
1 a
1 b
1 c
2 x
2 y
like this.
ANSWER:
SELECT
T.id, RIGHT(LEFT(T.csv,Number-1),
CHARINDEX(',',REVERSE(LEFT(','+T.csv,Number-1))))
FROM
master..spt_values,
your_table T
WHERE
Type = 'P' AND Number BETWEEN 1 AND LEN(T.csv)+1
AND (SUBSTRING(T.csv,Number,1) = ',' OR SUBSTRING(T.csv,Number,1) = '')
SOURCE:http://www.sql-server-performance.com/forum/threads/csv-to-columns.32141/#post-166346
TAG:
