Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split a column into several columns by delimiter Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 2:56 PM
Points: 1, Visits: 3
Hi, can anyone help me; I need a function/procedure where I can pass it a table name, column name and delimiter and have the column split into as many new columns as there instances of the delimiter. The number of delimiters can vary.

My table has an ID and some contact name columns and an address column. The address is in one column instead of several and this is separated by commas:

e.g.

ID Address1
1 Gdc, Plot No. B 1, MIDC, Software Technology Park, Talawade, Tal Haveli
2 Gate No 166 / 167, Pune Nagar Road, Sanaswadi, Shirur

I need this to be split like this:

Col1 Col2 Col3 Col4 Col5 Col6
GDC Plot No. B 1 MIDC Software Technology Park Talawade Tal Haveli
Gate No 166 / 167 Pune Nagar Road Sanaswadi Shirur


Thanks in advance for your help.

Post #1542289
Posted Monday, February 17, 2014 3:16 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
nickwrenchster (2/17/2014)
Hi, can anyone help me; I need a function/procedure where I can pass it a table name, column name and delimiter and have the column split into as many new columns as there instances of the delimiter. The number of delimiters can vary.

My table has an ID and some contact name columns and an address column. The address is in one column instead of several and this is separated by commas:

e.g.

ID Address1
1 Gdc, Plot No. B 1, MIDC, Software Technology Park, Talawade, Tal Haveli
2 Gate No 166 / 167, Pune Nagar Road, Sanaswadi, Shirur

I need this to be split like this:

Col1 Col2 Col3 Col4 Col5 Col6
GDC Plot No. B 1 MIDC Software Technology Park Talawade Tal Haveli
Gate No 166 / 167 Pune Nagar Road Sanaswadi Shirur


Thanks in advance for your help.



Sure this is feasible. The first thing you have to do is split your values apart on your delimiter. You can read the article in my signature about splitting strings.

I would ask why you are storing delimited values in a single column. This is a direct violation of 1NF and causes this sort of anguish.

Once you have split your string apart you will need to generate a dynamic cross tab. I would recommend doing this in the front end instead of in sql. However, if you really do need to do this in sql take a look at the two articles in my signature about cross tabs. You will need to use the dynamic version.

Concentrate first on getting the logic to work. There are a number of advanced topics you have to combine to pull this off. After you are able to get the output right, then concentrate on converting it to a procedure.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1542299
Posted Monday, February 17, 2014 4:52 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
I can recommend Jeff Moden's excellent article (and code) here:
Tally OH! An Improved SQL 8K “CSV Splitter” Function

I should add:

Using the splitter function will convert a string in the form:
A,B,C

to a table in the form:
ItemID Item
1 A
2 B
3 C

You can then use a PIVOT to convert it into a table in the form:
1 2 3
A B C

So split + pivot will do what you want.

A quick example:

select	*
from dbo.Split('A,B,C', ',')
pivot (
max(Item)
for ItemId in ([1], [2], [3])
) as X

Post #1542307
Posted Tuesday, February 18, 2014 9:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:34 PM
Points: 13,481, Visits: 12,342
The pivot works but the output needs to be dynamic. This is why I suggested using the dynamic cross tab, and you get the bonus of a cross tab being faster than a PIVOT most of the time.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1542637
Posted Tuesday, February 18, 2014 9:42 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:43 PM
Points: 3,667, Visits: 8,006
In the last test I did, pivot and cross tabs perform the same with a single aggregate. For multiple aggregates, cross tabs will be quite faster.
I still prefer the flexibility of cross tabs compared to the complexity of pivot.



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1542656
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse