14,695,784 members
See more:
i have the following table

sno data
1 delhi, bombay, calcutta
2 bangalore, chennai

my output should be

sno data
1 delhi
1 bombay
1 calcutta
2 bangalore
2 chennai

how can i perform this?
Posted

## Solution 2

See my past answer: A table cell contains multiple values separated by commas e.g A,B,C,D,A,E,C then how to remove the duplicate values[^]

And working example:
DECLARE @tmp TABLE (sno INT, data VARCHAR(30))

INSERT INTO @tmp (sno, data)
VALUES(1, 'delhi, bombay, calcutta'),
(2, 'bangalore, chennai')

;WITH CitiesCTE AS
(
SELECT sno, LEFT(data,CHARINDEX(',', data)-1) AS City, LTRIM(RTRIM(RIGHT(data,LEN(data) - CHARINDEX(',', data)))) AS Remainder
FROM @tmp
WHERE CHARINDEX(',', data)>0
UNION ALL
SELECT sno, LEFT(Remainder,CHARINDEX(',', Remainder)-1) AS City, LTRIM(RTRIM(RIGHT(Remainder,LEN(Remainder) - CHARINDEX(',', Remainder)))) AS Remainder
FROM CitiesCTE
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT sno, Remainder AS City, NULL AS Remainder
FROM CitiesCTE
WHERE CHARINDEX(',', Remainder)=0
)
SELECT *
FROM CitiesCTE
ORDER BY sno

Result:
1	delhi		bombay, calcutta
1	bombay		calcutta
1	calcutta	NULL
2	bangalore	chennai
2	chennai		NULL
v2

## Solution 1

If you have a look at this: Using comma separated value parameter strings in SQL IN clauses[^] - it's intended for IN clauses when you use a parameter to pass the comma delimited list, but the function returns a table, so it would be simple for you to modify that to produce your output. You then just SELECT from the table to return the values.

OK...try this:
Create the SQL function:
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[CSVToTableWithId] (@ID INT, @InStr VARCHAR(MAX))
RETURNS @TempTab TABLE
(tempid int IDENTITY(1,1) not null,
sno int not null,
data NVARCHAR(MAX))
AS
BEGIN
;-- Ensure input ends with comma
SET @InStr = REPLACE(@InStr + ',', ',,', ',')
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
WHILE PATINDEX('%,%', @INSTR ) <> 0
BEGIN
SELECT  @SP = PATINDEX('%,%',@INSTR)
SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
INSERT INTO @TempTab(sno, data) VALUES (@ID,@VALUE)
END
RETURN
END
GO

Then, use CROSS APPLY to fetch the values:
SELECT ca.sno, ca.data FROM MyTable t
CROSS APPLY (SELECT * FROM [CSVToTableWithId](t.sno, t.data)) ca
v2
kparun86 24-Feb-14 4:46am

Alright how to pass the values to this function?
OriginalGriff 24-Feb-14 4:51am

There is a section: "Using the code" which does that...
kparun86 24-Feb-14 5:03am

DECLARE @LIST VARCHAR(200)
SET @LIST = '1,3'
SELECT Id, Descr FROM CSVDemo WHERE Id IN (SELECT * FROM dbo.CSVToTable(@LIST))

but i don have any id.
i'm getting the following error.

Invalid column name 'Id'.
OriginalGriff 24-Feb-14 7:05am

Maciej Los 24-Feb-14 7:11am

+5
OriginalGriff 24-Feb-14 7:34am

For your continued pleasure, this answer is now available as a DVD, BluRay and AudioBook.

(Or possibly as a Tip, anyway! :laugh:)
Maciej Los 24-Feb-14 7:47am

Paul, i saw your Tip a long time ago, but i didn't know that you had published it using other media ;)
OriginalGriff 24-Feb-14 8:26am

:laugh:
Anything for a quick buck!

Nah - this is a different application of the same idea, so I knocked it up into a new tip so it's easier to find on Google if someone is looking. Assuming anyone looks at google instead of just posting a question, of course. :sigh: