Click here to Skip to main content
14,733,912 members
Please Sign up or sign in to vote.
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.
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.

If you have an appropriate version of SQL Server you can apparently use recursion ...[^]
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
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:

above example:

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
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 ...[^]
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[^] 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.

Check the following link for Split Function... then u will use one variable for Get the ColumnA value and place in final Select Statement.
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[^]


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