Split CSV data in n column(s)

Question

4.00/5 (3 votes)

See more:

SQL-server-2000

SQL-Server-2005

, +

I want to create such function in SQL Server that takes following paramater
1. Data (Data in CSV format)
2. Delimiter (Delimiter used to separate data)
3. Number Of column (specify number of columns output table should have)

Value of parameter
Data - 1,2,3,4,5,6
Delimiter - ,(Comma)
Number of column

output for Number of column=2:

Col1	Col2
1	2
3	4
5	6

output for Number of column=3:

Col1	Col2	Col3
1	2	3
4	5	6

Thanks in advance...! :)

Posted 22-Apr-11 0:41am

Dave Paras

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MaulikDusara · Answer 1 · 2011-04-22T01:15:00

This will convert your comma delimited string to one column, hope this will help you

ALTER FUNCTION [dbo].[split] (@p_ids VARCHAR(8000))
RETURNS @Ids TABLE (Ids BIGINT)
/*Returns a result set that contains all the Ids as row*/
AS
BEGIN
	IF @p_ids IS NULL
		BEGIN
			RETURN
		END
		
	DECLARE @id_str		VARCHAR(15)
	DECLARE @l_str		VARCHAR(8000)
	DECLARE @pos		INT
	SET @l_str = @p_ids
	SET @pos = CHARINDEX(',', @l_str)
	WHILE @pos > 0
		BEGIN
			SET @id_str = RTRIM(LTRIM(SUBSTRING(@l_str, 1, @pos-1)))
			INSERT INTO @Ids (Ids) VALUES (@id_str)
			SET @l_str = LTRIM(RTRIM(SUBSTRING(@l_str, @pos+1, 8000)))
			SET @pos = CHARINDEX(',', @l_str)
		END
	SET @id_str = LTRIM(RTRIM(@l_str))
	INSERT INTO @Ids (Ids) VALUES (@id_str)
	RETURN 
END

E.F. Nijboer · Answer 2 · 2011-04-22T01:17:00

Solution 2

Here you have an example of how to split a csv string into separate values. You can simply make a small change by adding an extra parameter to specify how many columns each row has and declare a counter variable to keep track of the current column number so you know when you have a complete row.
http://www.saqib-ansari.com/2010/04/split-csv-string-into-table-in-sql-server.html[^]

Good luck!

Posted 22-Apr-11 1:17am

E.F. Nijboer

Comments

Dave Paras 22-Apr-11 7:44am

Yeah I know.
But main prob is that how to create dynamic columns.
Functionality provided in link has already been achieved.
Thanks for ans.

E.F. Nijboer 22-Apr-11 7:47am

Have a look at this link. I think you can find some good info there:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648

mhwasim · Answer 3 · 2011-05-18T02:38:00

Use the followinf logic to dynamiccaly add the column based on field value
I have used this logic for the same prolem....

DECLARE @Sql VARCHAR(8000), @Val VARCHAR(50)
SET @Sql = ''

DECLARE cur_AltTbl CURSOR FOR SELECT DISTINCT <<select field name to be converted as column>>FROM <<table name>>

OPEN cur_AltTbl
FETCH NEXT FROM cur_AltTbl INTO @Val

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Sql = 'ALTER TABLE <<Table on which synamic rows to be added>> ADD ' + @Val + ' INT'
PRINT @Sql
--INSERT INTO EmpDateShift
EXEC (@Sql)

FETCH NEXT FROM cur_AltTbl INTO @Val
END

CLOSE cur_AltTbl
DEALLOCATe cur_AltTbl
.........

Thanks

Split CSV data in n column(s)

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0