split string column into multiple records

Question

0.00/5 (No votes)

See more:

I need to transform thousands of source records into multiple records as shown in the bottom end, that have the following form:

column A Column B

record1 12,13,14,18

the result of such source records should get transformed into the following 4 records down below. Means: A record should get created for every comma delimited number in column B. The string in column B contains different amount of comma delimited numbers. sometimes 4 numbers sometimes 25 numbers. Result of the record transformations should look for the example above like this:

column A column B

record1 12

record1 13

record1 14

record1 18

i dont want to use while loop.

Posted 26-Apr-13 0:45am

softprga

Add a Solution

Comments

Maciej Los 26-Apr-13 7:01am

It's interesting, how do you want to achieve that without loop?

Maciej Los 26-Apr-13 7:18am

OK, there still is a hope, but i need more example data. Please, improve question and paste 20 rows of data. Till now column A is not separated by comma. Is it true?

RedDk 26-Apr-13 14:19pm

Perhaps a veiled "sqlce" question ... in which case I'd HAVE to say that what you want to do IS impossible.

RedDk 26-Apr-13 14:21pm

Perhaps a veiled "sqlce" question ... in which case I'd HAVE to say that waht you want to do is impossible.

2 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)

CHill60 · Answer 1 · 2013-04-26T01:06:00

Solution 1

If you have an appropriate version of SQL Server you can apparently use recursion ... http://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows[^]
I've tried this on VS2005 and it doesn't work for me but the principle could be expanded into a function I guess. Nevertheless the chap that needed the solution marked this as an answer

Posted 26-Apr-13 1:06am

CHill60

Comments

Maciej Los 26-Apr-13 7:11am

Chill, this is wrong answer ;(
Please, read question carefully: B contains different amount of comma delimited numbers. sometimes 4 numbers sometimes 25 numbers.
OP requirements:

rec1,1,2,3,15
rec2,2,5,8,98,44,32,55

above example:

rec1,'1,2,3,15'
rec2,'2,5,8,98,44,32,55'

Do you see the difference?

CHill60 26-Apr-13 7:19am

To be honest I can't see the difference ... the Answer marked with 16 votes and a tick is using variable length sample data ... 3 numbers, then 2 numbers, then 3 then none. The only thing is the way the results are being displayed - column B still contains '12,13,14,18'

softprga 26-Apr-13 8:07am

http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html
it works,but can you explain

CHill60 26-Apr-13 8:52am

Essentially the first bit (with tmp ...) creates a the table tmp and seeds it with a starting set of data of "Record1", "12" and "13,14,18". The next bit adds rows to tmp of "Record1", "13", "14,18" and keeps on going until it gets to "Record1", "18", "". I.e. the last column is what's left to process so the result is repeatedly used to get the final results
This guy explains it better than I do ... http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/[^]

CHill60 26-Apr-13 9:13am

Sorry - I thought you were referring to my solution - I've just spotted the link in your comment (hyperlinked here from an email and missed it). Try looking at http://beyondrelational.com/modules/2/blogs/114/posts/14617/delimited-string-tennis-anyone.aspx[^] which is cross-referenced from the link you've posted. It may explain more - it definitely goes on about performance when using XML for this sort of thing. As to a detailed explanation of how it works you'd better ask the guy who posted it - I don't have the right version of SQL here to be digging into it.

gvprabu · Answer 2 · 2013-04-26T02:25:00

Hi,

Check the following link for Split Function... then u will use one variable for Get the ColumnA value and place in final Select Statement.
Like

SQL

DECLARE @ColA VARCHAR(1000), @ColB VARCHAR(1000)
SELECT @ColA = columnA, @ColB = columnB  FROM Table_Name
SELECT @ColA,ColumnValues FROM fnSplit(@ColB ,',')

If any Clarifications, Check the following link
Split function error - The statement terminated. The maximum recursion 100 has been exhausted before statement completion[^]

Regards,
GVPRabu