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
RaahaugenAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
your query lokk like thsi .. no UDF required

SELECT A.[Center], A.[Initials],A.Types,Split.a.value('.', 'VARCHAR(100)') AS Zips   FROM
( SELECT c.Name as Center
            ,ta.Initials
            ,ta.MeetingTypes as Types,
            CAST ('<M>' + REPLACE(ta.GeoAreas, ',', '</M><M>') + '</M>' AS XML) AS String    
           

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 = '_' ) as A CROSS APPLY String.nodes ('/M') AS Split(a);
0
 
AnujSQL Server DBACommented:
0
 
RaahaugenAuthor Commented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Pratima PharandeCommented:
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
0
 
RaahaugenAuthor Commented:
@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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
Pratima PharandeCommented:
glad to help you :)
0
 
AnujSQL Server DBACommented:
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.
0
 
RaahaugenAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.