asked on

SQL Query: Split comma seperated values into rows

Hello Experts!

I have the following SQL Query, where the field ta.GeoAreas contains comma separated values (reason is it needs to support Sharepoint, at least that is my thought).

As of now the query creates a row for each TiedAgent. I would like to extend the table to include a row for every TiedAgent and Zipcode connection. So for instance, if the tied agent ABC has GeoAreas "1111,2222,3333", instead of the row

[Center] / "ABC" / [MeetingTypes] / "1111,2222,3333"

I would like three rows to be created like this:

[Center] / "ABC" / [MeetingTypes] / "1111"
[Center] / "ABC" / [MeetingTypes] / "2222"
[Center] / "ABC" / [MeetingTypes] / "3333"

Please help me on that one?

SELECT c.Name as Center
            ,ta.Initials
            ,ta.MeetingTypes as Types
            ,ta.GeoAreas as Zips

FROM [mbw-db].[dbo].[TiedAgent] ta 

JOIN [mbw-db].[dbo].[Center] c 
ON ta.centerID = c.ID

WHERE ta.Status = 'Aktiv' AND NOT ta.Initials = '_'

Open in new window

The Server is running SQL Server 2008 - it is not possible to create UDF's, at least not on serverside - and as I've figured, this is necessary in order to use them?

The query will have to be used in an excel file, if this makes any difference...

Thanks in advance

/Raahaugen

Anuj

Please check Jeff Moden's latest method (8K CSV Spitter Function).

Raahaugen

ASKER

Hi anujnb,

First of all, thankyou for your attention on this one.

As far as I see it, the article you refer to mention namely a UDF as the solution to the problem - please correct me if I'm wrong.

I don't know how to use a UDF - I think it has to be set up server side in order to use it client side, is that not right?

The ideal solution would be to extend the query as put in the code snippet above.

Thanks in advance.

/Raahaugen

Pratima

hey try this in your case

create table #tmp ( id int ,val varchar(20))
insert into #tmp (id,val)values(1,'1,2,3')
insert into #tmp (id,val)values(2,'1,2,3')
select * from #tmp

SELECT A.[id],Split.a.value('.', 'VARCHAR(100)') AS String FROM
(SELECT [id],CAST ('<M>' + REPLACE([val], ',', '</M><M>') + '</M>' AS XML) AS String FROM #tmp) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
drop table #tmp

Refer
http://road-blogs.blogspot.com/2010/05/converting-single-comma-separated-row.html

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Raahaugen

ASKER

@pratima_mcs: It works as a charm....! Lovely. Thanks!!!

@anujnb: If you have a working solution as well, feel free to post it, and you can share points. Otherwise I will give all points to pratima

Have a nice day!

/Raahaugen

Guy Hengel [angelIII / a3]

I also wrote an article about this kind of situations:
https://www.experts-exchange.com/Database/Miscellaneous/A_1536-delimited-list-as-parameter-what-are-the-options.html

Pratima

glad to help you :)

Anuj

8K CSV spliter is the fastest and optimal solution available till now, previously "tally table" was the best, a lot of research is going on about splitting CSV values using T-SQL.

Another recommendation is to create a CLR function that splits the values, and call that inside SQL Server, this is also a best solution.

Raahaugen

ASKER

Thanks for working solution

To all the others: Thanks for good links, that I will use when I have some time off for reading, although you didn't bring me working solutions as did pratima.

Also I still don't get if UDF functions has to be created serverside or if they can be created and used in a single sql query, but anyways...

Thanks again!

/Raahaugen