Click here to Skip to main content
Rate this: bad
good
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 1:45am
Comments
Maciej Los at 26-Apr-13 7:01am
   
It's interesting, how do you want to achieve that without loop?
Maciej Los at 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 at 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 at 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 at 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 at 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 at 26-Apr-13 8:07am
   
http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html
it works,but can you explain
CHill60 at 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 at 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 2

  Permalink  
v2
Comments
Maciej Los at 26-Apr-13 8:22am
   
Is this an answer or comment?
To post comment use "Have a comment or question" widget!
softprga at 26-Apr-13 8:23am
   
both
softprga at 26-Apr-13 8:24am
   
this output correct.but not understand query
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
0 OriginalGriff 325
1 Sergey Alexandrovich Kryukov 289
2 CPallini 275
3 DamithSL 260
4 Maciej Los 215
0 OriginalGriff 5,455
1 DamithSL 4,422
2 Maciej Los 3,860
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,010


Advertise | Privacy | Mobile
Web04 | 2.8.141216.1 | Last Updated 26 Apr 2013
Copyright © CodeProject, 1999-2014
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