Return comma separtated as rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return comma separtated as rows

Author

Topic

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-18 : 12:37:30

I have a table with the following columns:
FK_ReferenceiD,FK_FieldID,Value

The Value will be a comma separated value that I would like to split and return as indicidual rows. For example:
[FK_ReferenceiD], [FK_FieldID],[Value]
154 847 12,84,798

will be returned in a select script as:
FK_ReferenceID FieldID Value
154 847 12
154 847 84
154 847 798

I will be using the FK_FieldID as my where clause, (where FK_FieldID = '847')

Can someone help out? The easier the better. :)

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-18 : 12:39:29

Search for the various parse functions available here.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 12:42:14

similar thread

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-18 : 12:51:19

visakh, looking at your post, it looks like it is assuming I already know the values to parse. because the values in the comma separated string are unknown, is there a way of doing this without known values. There could be thousands of rows that contain comma separated values.

The values in the comma separated field will all be numbers, and no text, (with a comma as the only exception, of course)

Thanks

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-18 : 12:54:22

Sorry, I think it is not assuming I know the values. I may have jumped the gun, visakh. Let me play around with that script and see if I can get it to work.

Thanks,

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-18 : 13:25:05

OK, sorry for being such a newbie on this.

When I try the script, I get an error about declaring @tbl

I have a few tables in this database, but just need to query against the stringvalues table and where the FK_FieldID is a certain single value. The data is already existing, so I'm not sure how to declare this table.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-08-18 : 13:46:25

You don't need to use @tbl. That part was included in the post to illustrate the solution. @tbl represents whatever table contains your csv data.

You just care about this:
SELECT t.InvoiceId,b.Val FROM @tbl t
CROSS APPLY ParseValues(t.Dc)b

Change it to your actual table(s).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-18 : 14:00:20

Thanks Tara.

I can see that it starts, but then it generates this error:
Msg 9420, Level 16, State 1, Line 1
XML parsing: line 1, character 20, illegal xml character

prior to this error, I am actually seeing the results I want, but they disappear when this error is generated.

here is the modified script:
SELECT t.FK_ReferenceID,b.Val FROM stringvalues t
CROSS APPLY ParseValues(t.Value)b where FK_FieldID = '1441'

If any values are in the database referencing Field 1441, then they will be either a single value or a comma separated value.

If we can get this to work, I guarentee I'll be subscribing to your blog! :)

obsoletedude
Starting Member

31 Posts

Posted - 2008-08-18 : 14:08:45

Never mind, i was able to resolve this with an extra filter. Tara and visakh16, I owe you two big time!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources