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
GO
CREATE TABLE SampleTable (ID INT, Value VARCHAR(50))

INSERT INTO SampleTable VALUES(1,'1001'),
                              (2,'1002,1003'),
                              (3,'1004,1005,1006'),
                              (4,'1007,1008,1009,1010'),
                              (5,'1011,1012,1013,1014,1015')

 /*  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
GO

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 */

DECLARE @Start INT,
        @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)
BEGIN
   
    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
        BEGIN
      
            SET @StringStart = 1
            SET @StringLength = (SELECT LEN(@String))
          
            SET @To = 1
          
            WHILE (@StringStart <= @StringLength)
            BEGIN
              
                IF (SELECT SUBSTRING(@String,@StringStart,@To)) <> ','
                BEGIN
                    SET    @OutputString = @OutputString + (SELECT SUBSTRING(@String,@StringStart,@To))
                END
                ELSE
                BEGIN
                    INSERT INTO #ResultTable(ID,Value)
                    SELECT @ID,
                           @OutputString
                    SET @OutputString = ' '
                END  
              
            SET @StringStart = @StringStart + 1
                  
            END
            END
            IF (@OutputString <> ' ')
            INSERT INTO #ResultTable(ID,Value)
                SELECT @ID,
                       @OutputString
            ELSE
            BEGIN
                INSERT INTO #ResultTable(ID,Value)
                SELECT @ID,
                       @String
            END
          
    SET @Start = @Start + 1  
      
    DELETE FROM SampleTable_Dup WHERE ID = @ID AND Value = @String   
END
   

SELECT * FROM #ResultTable

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

Thanks,
Narendra