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.

Answer 2

You can write a table function, and join your column to it with CROSS APPLY. Here's my version.

CREATE FUNCTION dbo.Splitter(@text nvarchar(max), @separator nvarchar(100))
RETURNS @result TABLE (i int, value nvarchar(max))
AS
BEGIN
    DECLARE @i int
    DECLARE @offset int
    SET @i = 0

    WHILE @text IS NOT NULL
    BEGIN
        SET @i = @i + 1
        SET @offset = charindex(@separator, @text)
        INSERT @result SELECT @i, CASE WHEN @offset > 0 THEN LEFT(@text, @offset - 1) ELSE @text END
        SET @text = CASE WHEN @offset > 0 THEN SUBSTRING(@text, @offset + LEN(@separator), LEN(@text)) END
    END
    RETURN
END

Answer 3

Another one of many string splitting functions out there. This is sort of similar to @Byron Whitlock's answer but instead of using master..spt_values uses a cte to generate a numbers table. SQL Server 2005 onwards.

CREATE TABLE dbo.Table1 
(
    Col1        CHAR(1),
    Col2        CHAR(1),
    Col3        CHAR(1),
    Col4        VARCHAR(50)
)
GO

INSERT INTO dbo.Table1 VALUES ('A','B','C','1,2,3')
GO

SELECT * FROM dbo.Table1;
GO

WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
Numbers AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L3)
SELECT  Col1, Col2, Col3,        
        LTRIM(RTRIM(SUBSTRING(valueTable.Col4, nums.n, charindex(N',', valueTable.Col4 + N',', nums.n) - nums.n))) AS [Value]
FROM   Numbers AS nums INNER JOIN dbo.Table1 AS valueTable ON nums.n <= CONVERT(int, LEN(valueTable.Col4)) AND SUBSTRING(N',' + valueTable.Col4, n, 1) = N','