Click here to Skip to main content
14,665,360 members
Rate this:
Please Sign up or sign in to vote.
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

Rate this:
Please Sign up or sign in to vote.

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
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:
Rate this:
Please Sign up or sign in to vote.

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100