Click here to Skip to main content
12,399,975 members (47,925 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL Server
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
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.
Rate this: bad
 
good
Please Sign up or sign in to vote.

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

Solution 3

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
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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 12 Feb 2016
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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