I want to split the strings seperated with comma and save as new row in SQL Server

Question

Table1:

Col1 | Col2
-----+-------------------
AAA  | BB1,KB2,YB3,BP4

Table2:

Col1 | Col2
-----+--------
AAA  | BB1 
AAA  | KB2
AAA  | YB3
AAA  | BP4

Can someone help me split and save the results this way using SQL Server 2008 R2 or SSIS 2008 R2?

Answer 1

Try this

DECLARE @T TABLE
(
    Col1 VARCHAR(10),
    Col2 VARCHAR(100)
)

INSERT INTO @T
VALUES('AAA','BB1,KB2,YB3,BP4')

;WITH CTE
AS
(
    SELECT
    Col1,
    Col2_1 = SUBSTRING(Col2,CHARINDEX(',',Col2)+1,LEN(Col2)),
    Col2 = SUBSTRING(Col2,1,CHARINDEX(',',Col2)-1)
    FROM @T

    UNION ALL

    SELECT
        Col1,
        Col2_1 = 
                CASE WHEN CHARINDEX(',',Col2_1) > 0
                            THEN SUBSTRING(Col2_1,CHARINDEX(',',Col2_1)+1,LEN(Col2_1))
                        ELSE NULL END,
        Col2 = CASE WHEN CHARINDEX(',',Col2_1) > 0
                            THEN SUBSTRING(Col2_1,1,CHARINDEX(',',Col2_1)-1)
                        ELSE Col2_1 END
        FROM CTE 
            WHERE ISNULL(Col2_1,'')<>''

)
SELECT
Col1,
Col2
FROM CTE