Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to join to a comma delimited list Expand / Collapse
Author
Message
Posted Monday, June 16, 2014 2:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 AM
Points: 100, Visits: 333
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

Post #1581761
Posted Monday, June 16, 2014 2:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:00 PM
Points: 1,874, Visits: 18,433
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 !
__________________________________________________________________
Post #1581783
Posted Monday, June 16, 2014 2:31 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 1:57 PM
Points: 3,312, Visits: 7,142
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1581823
Posted Monday, June 16, 2014 3:17 PM This worked for the OP Answer marked as solution


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 12:56 PM
Points: 553, Visits: 2,575
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


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1581857
Posted Monday, June 16, 2014 3:49 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:47 AM
Points: 100, Visits: 333
Thank you.
I also found this:
http://stackoverflow.com/questions/13873701/convert-comma-separated-column-value-to-rows
Post #1581897
Posted Monday, June 16, 2014 8:26 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1582006
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse