Tuesday, 2 October 2012

Getting Comma Seperated Values Into Rows

Recently one of my friend got the following requirement

Actual Table :

OutPut Table

Here all the comma Separated values in each row have to be distributed into individual rows corresponding ID value.

of course this task can be done in multiple ways , i have come up with follwoing script

Source Code :

/* Creating SampleTable with 5 Rows */

IF EXISTS(SELECT 1 FROM sys.tables WHERE TYPE = 'U' AND name = 'SampleTable')
DROP TABLE SampleTable
CREATE TABLE SampleTable (ID INT, Value VARCHAR(50))

INSERT INTO SampleTable VALUES(1,'1001'),

 /*  Creating Copy of SampleTable For Our Query Processing */

IF EXISTS(SELECT 1 FROM sys.tables WHERE TYPE = 'U' AND name = 'SampleTable_Dup')
DROP TABLE SampleTable_Dup

SELECT * INTO SampleTable_Dup FROM SampleTable

SELECT * FROM SampleTable_Dup

/* Creating ResultTable which is Temporary to Store the Result */

DROP TABLE #ResultTable

CREATE TABLE #ResultTable (ID INT,Value VARCHAR(50))

/* Actual Logic Starts */

        @Count INT,
        @ID INT,
        @String VARCHAR(50),
        @StringStart INT,
        @StringLength INT,
        @OutputString VARCHAR(50),
        @To INT
SET @Start = 1
SET @Count = (SELECT COUNT(1) FROM SampleTable_Dup)

WHILE (@Start < = @Count)
    SET @OutputString = ''
    SET @ID = (SELECT TOP 1 ID FROM    SampleTable_Dup)
    SET @String = (SELECT TOP 1 Value FROM SampleTable_Dup)
    IF (SELECT CHARINDEX(',',@String)) > 0
            SET @StringStart = 1
            SET @StringLength = (SELECT LEN(@String))
            SET @To = 1
            WHILE (@StringStart <= @StringLength)
                IF (SELECT SUBSTRING(@String,@StringStart,@To)) <> ','
                    SET    @OutputString = @OutputString + (SELECT SUBSTRING(@String,@StringStart,@To))
                    INSERT INTO #ResultTable(ID,Value)
                    SELECT @ID,
                    SET @OutputString = ' '
            SET @StringStart = @StringStart + 1
            IF (@OutputString <> ' ')
            INSERT INTO #ResultTable(ID,Value)
                SELECT @ID,
                INSERT INTO #ResultTable(ID,Value)
                SELECT @ID,
    SET @Start = @Start + 1  
    DELETE FROM SampleTable_Dup WHERE ID = @ID AND Value = @String   

SELECT * FROM #ResultTable

Friends, if any body have faced the same problem , Please share with your solution .


No comments:

Post a Comment