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
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
No comments:
Post a Comment