Click here to Skip to main content
14,693,130 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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
Comments
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
   
Answer updated
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:
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900