How to join to a comma delimited list

Question

How to join to a comma delimited list

RightOnTarget

Hall of Fame

Points: 3041
More actions
June 16, 2014 at 2:09 pm

#309158

Hi all,
I have a table:
id pallet color
1 a red,blue
2 b yellow
and function that will split colors:
declare @colors varchar(100) = 'red,blue'
select * from SeparateValues (@colors,',')
will return
red
blue
Question: how do i join to show:
pallet color
a red
a blue
b yellow

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

does your function return id or pallet?...this would possibly provide a "join"

maybe better if you posted create table/insert sample data scripts and your expected results...am sure this will result is a tried and tested answer for you.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You might be looking for something like this:

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

Read the article and ask any questions that you might have.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

rightontarget (6/16/2014)
Hi all,
I have a table:
id pallet color
1 a red,blue
2 b yellow
and function that will split colors:
declare @colors varchar(100) = 'red,blue'
select * from SeparateValues (@colors,',')
will return
red
blue
Question: how do i join to show:
pallet color
a red
a blue
b yellow

I think you are looking for this:

DECLARE @YourTable TABLE

(

id int primary key,

pallet char(1) not null,

color varchar(100) not null

)

INSERT @YourTable (id, pallet, color)

VALUES(1, 'a', 'red,blue'),(2, 'b', 'yellow');

SELECT pallet, Item

FROM @YourTable t

CROSS APPLY SeparateValues(color,',')

Edit: Added SQL Code tag thingee around my solution

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 4

Thank you.

I also found this:

http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 5

rightontarget (6/16/2014)
Thank you.
I also found this:
http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows

That uses both an XML conversion and concatenation. That also makes it comparatively slow. Here's a comparison chart for 1,000 CSVs. Consider using the DelimitedSplit8K function found in the "resources" link at the bottom of the following article. The Black line is the new DelimitedSplit8K (and it's actually 10-20% faster than that thanks to an additional change).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)