Split delimited string to use in sub query

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

SQL Server 2005 Forums

Transact-SQL (2005)

Split delimited string to use in sub query

Author

Topic

biggunn
Starting Member

4 Posts

Posted - 2011-07-30 : 13:19:47

Here is the scenario:

Product
ProductID
RelatedProducts

ProductSection
SectionID
ProductID

Section
SectionID
ParentSectionID

Product.RelatedProducts is a comma delimted string of ProductID's that will have 0 to 1000 items in it.

The basics of what I am trying to do is this (I know this isn't correct):

Select SectionID, Name From Section Where ParentSectionID = 86 and SectionID In(Select SectionID From ProductSection Where ProductID IN (items from RelatedProducts list)

I am needing to iterate through each item in RelatedProducts, to get the productID, in order to get the sectionID's that are associated with those products, so I can ultimately get the sectionID's that have a ParentSectionID = 86

I have tried SPLIT, but wasn't able to make it work, although I could have been doing it wrong. Any genius's out there ready to take on this challenge? Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-30 : 13:24:39

see

http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-30 : 15:36:18

I don't see the column [Name] in any of your table definitions, I am assuming that it is a column in the Section table. In any case, you may be able to write the query without actually doing the parsing.

SELECT
	s.SectionId,
	s.[Name]
FROM
	Sections s
	INNER JOIN ProductSection ps ON
		ps.SectionId = s.SectionId
	INNER JOIN Product p ON
		','+p.RelatedProducts+',' LIKE '%,'+ps.ProductId+',%'
WHERE
	s.ParentSectionId = 86;

keentolearn
Starting Member

21 Posts

Posted - 2011-08-01 : 09:20:15

Hi, I have a list of contacts with contactID, name, job title, address, email and category columns. A contact can have 10-20 categories. I wanted to create a user defined funtion to put all the categories for a contact in a single cell separated by semicolon. However, I only have read-only access therefore cna not create a user defined function. Is there a way to return all categories for a contact in a new column or if easier all in one cell separated with semicolon. So where the contactID is the same and there are multiple rows for each category..

Many thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-01 : 09:56:38

you can do it. use like


SELECT contactID,
name,
jobtitle,
address,
email,
STUFF((SELECT ';'+category FROM Table WHERE contactID = t.ContactID FOR XML PATH('')),1,1,'') AS CatList
FROM (SELECT DISTINCT contactID,name,jobtitle,address,email FROM YourTable)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources