Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query: Split comma seperated values into rows

Posted on 2012-09-18
9
Medium Priority
?
3,878 Views
Last Modified: 2014-07-08
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
0
Comment
Question by:Raahaugen
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:Anuj
ID: 38408874
0
 

Author Comment

by:Raahaugen
ID: 38408906
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38408919
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 38408932
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
 

Author Comment

by:Raahaugen
ID: 38408953
@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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38408962
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38408963
glad to help you :)
0
 
LVL 15

Expert Comment

by:Anuj
ID: 38409038
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
 

Author Closing Comment

by:Raahaugen
ID: 38413254
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question