February 17, 2014 at 2:43 pm
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.
February 17, 2014 at 3:16 pm
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 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/
February 17, 2014 at 4:52 pm
I can recommend Jeff Moden's excellent article (and code) here:
Tally OH! An Improved SQL 8K “CSV Splitter” Function[/url]
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*
fromdbo.Split('A,B,C', ',')
pivot(
max(Item)
for ItemId in ([1], [2], [3])
) as X
February 18, 2014 at 9:11 am
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 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/
February 18, 2014 at 9:42 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply