Splitting a single column into multiple rows

Question

Post reply

Splitting a single column into multiple rows

mdaddiction

Grasshopper

Points: 13
More actions
June 13, 2014 at 8:53 am

#309144

I have a table that holds multiple values in a single column. I'm trying to write a script to normalize data in a single query. I want to break apart the comma delimited values and insert them as individual rows in a child table. The column could have any number of values.
CREATE TABLE #myTable
(
Yr INT
Value VARCHAR(MAX)
)
CREATE TABLE #myTable2
(
ID INT
Yr INT
Value VARCHAR(MAX)
)
INSERT INTO #myTable
(Yr, Value)
SELECT 2005, 'value1,value2' UNIONALL
SELECT 2006, 'value3,value4,value5' UNIONALL
SELECT 2007, 'value6'
Output should look like this
#myTable2
ID Yr Value
----------------
1 2005 value1
2 2005 value2
3 2006 value3
4 2006 value4
5 2006 value5
6 2007 value6

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

http://www.sqlservercentral.com/articles/Tally+Table/72993/

see code in resources at bottom of article

edit> corrected IFCode shortcut

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

J Livingston SQL (6/13/2014)
http://www.sqlservercentral.com/articles/Tally+Table/72993/
see code in resources at bottom of article

Just correcting the IFCode to make it easier.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Luis Cazares (6/13/2014)
J Livingston SQL (6/13/2014)
http://www.sqlservercentral.com/articles/Tally+Table/72993/
see code in resources at bottom of article
Just correcting the IFCode to make it easier.

thanks Luis > post corrected

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange SSC Guru Points: 286609 More actions · Answer 4

While I agree that the DelimitedSplit8K function is likely to be of help here I want to point out that the datatype containing the csv data is varchar(max) which does NOT play nicely with that function.

OP, I know this is your first post and I want to congratulate you on posting ddl and sample data in a consumable format. That is something we struggle with many people to understand the importance of. KUDOS!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

mdaddiction Grasshopper Points: 13 More actions · Answer 5

Thanks! I haven't implemented the solution yet but it looks exactly like what I'm want.

mdaddiction Grasshopper Points: 13 More actions · Answer 6

Sean, thanks for the advice and the KUDOS. I've been tasked with cleaning up the DB of my predecessor. I'll actually be implementing the solution for several different data types. Everything from TEXT to badly formed XML saved in a VARCHAR. Unfortunately this problem threw me for loop.

Sean Lange SSC Guru Points: 286609 More actions · Answer 7

mdaddiction (6/13/2014)
Sean, thanks for the advice and the KUDOS. I've been tasked with cleaning up the DB of my predecessor. I'll actually be implementing the solution for several different data types. Everything from TEXT to badly formed XML saved in a VARCHAR. Unfortunately this problem threw me for loop.

Sounds like a challenge to deal with. The DelimitedSplit8K will work great if you can avoid the max datatypes. Maybe you can just change the column? I can't tell you how many times I have seen the max type used and the amount of actual content is nowhere near that much.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

Sean Lange (6/13/2014)
While I agree that the DelimitedSplit8K function is likely to be of help here I want to point out that the datatype containing the csv data is varchar(max) which does NOT play nicely with that function.
OP, I know this is your first post and I want to congratulate you on posting ddl and sample data in a consumable format. That is something we struggle with many people to understand the importance of. KUDOS!!!

Thanks Sean...good catch...missed that !

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 9

Sean Lange (6/13/2014)
While I agree that the DelimitedSplit8K function is likely to be of help here I want to point out that the datatype containing the csv data is varchar(max) which does NOT play nicely with that function.

Since the datatype of the input parameter is VARCHAR(8000), it'll work just fine if the data is actually less than 8K. The problem comes into play when someone changes the datatype within the function itself.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286609 More actions · Answer 10

Jeff Moden (6/13/2014)
Sean Lange (6/13/2014)
While I agree that the DelimitedSplit8K function is likely to be of help here I want to point out that the datatype containing the csv data is varchar(max) which does NOT play nicely with that function.
Since the datatype of the input parameter is VARCHAR(8000), it'll work just fine if the data is actually less than 8K. The problem comes into play when someone changes the datatype within the function itself.

True. I know that but did not do a good job explaining my concern. Thanks Jeff!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/