Hi All,

In the above code, i have noticed a problem that in the #Numbers table,

It is not a problem, just suppose if the query results more than the no.of the rows in the identity column, then i will not give.

In this scenario, we have inserted 100 numbers in the #numbers, so it will return up to 100.

...

one person suggested the other logic... in the same fourm

CREATE TABLE #t2(ID INT IDENTITY, Column1 NVARCHAR(100))

CREATE TABLE #t3(Column1 NVARCHAR(100))

INSERT INTO #t2

SELECT 'Value1, Value2'

UNION ALL SELECT 'Value1, Value3'

UNION ALL SELECT 'Value1, Value2'

UNION ALL SELECT 'Value4, Value5'

UNION ALL SELECT 'Value1, Value3'

UNION ALL SELECT 'Value1, Value2'

UNION ALL SELECT 'Value4, Value5'

SELECT * FROM #t2

UPDATE #t2 SET Column1 = replace(Column1,' ','')+','

SELECT * FROM #t2

DECLARE @Values NVARCHAR(100), @ID INT

SELECT @ID = MIN(ID) FROM #t2

WHILE @ID is NOT NULL

BEGIN

SELECT @Values = SUBSTRING(Column1, 1,PATINDEX('%,%',Column1)) FROM #t2 WHERE ID = @ID

WHILE ISNULL(@Values,'') <> ''

BEGIN

INSERT INTO #t3

SELECT @Values

UPDATE #t2 SET Column1 = STUFF(Column1, 1, LEN(@Values), '') WHERE ID = @ID

SELECT @Values = SUBSTRING(Column1, 1,PATINDEX('%,%',Column1)) FROM #t2 WHERE ID = @ID

END

SELECT @ID = MIN(ID) FROM #t2 WHERE ID > @ID

END

UPDATE #t3 SET Column1 = replace(Column1,',','')

SELECT DISTINCT *

FROM #t3

DROP TABLE #t2

DROP TABLE #t3