sql server - Split one column into multiple rows

Question

Can anyone tell me how to accomplish this? A column in my table, in some instances, contains comma separated values. If it does, I need to create new rows for these values.

Moreover, as an example, a table contains 1 row and 4 columns Col1 | Col2 | Col3 | Col4 with the following values A | B | C | 1,2,3 respectively. So, Col4 contains the string '1,2,3' and I need to break up the comma separated values and place them on lines of their own so the table would then contain 1 rows where 1 2 and 3 are on lines of their own in Col4.

Answer 1

I think you can do this:

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)  = '') 

Code was shamelessly stolen from this site.