Creating a comma delim. list in a sub-query? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

SQL Server Development (2000)

Creating a comma delim. list in a sub-query?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-24 : 09:25:38

Brian writes "I have read your articles on creating comma delimited lists. The one on creating a stored procedure seems to be the one that would most likely apply to my query though, I'm not exactly sure if it will work.

I have attached my SQL below. The sub query is my main focus on it returning the list I need, though obvoiusly it won't work. Am I going about this the wrong way, missing something simple,... is there a solution to the problem?

Thanks, Brian.


SET		NOCOUNT ON;
DECLARE @svcSelList varchar(100)

SELECT	Service_Feature_ID, Service_Feature_Name, Selected_Feature_Weight,
	(
	SELECT	@svcSelList = COALESCE(@svcSelList + ',', '') +
		CAST(tmp.Selected_Feature_ParentID AS varchar(3))
	FROM	Invite_Selected_Features AS tmp
	WHERE	tmp.Selected_Feature_ParentID IN (#svcList#)
	AND
		tmp.Selected_Feature_FeatureID = Selected_Feature_FeatureID
	AND
		tmp.Selected_Feature_Key =	1
		) AS parent_ID_List

FROM	Invite_Selected_Features,
	Invite_Service_Features

WHERE	Selected_Feature_ParentID IN (#svcList#)
AND
	Service_Feature_ID = Selected_Feature_FeatureID
AND
	Selected_Feature_Key =	1
GROUP BY
Service_Feature_ID, Selected_Feature_Weight, Service_Feature_Name
ORDER BY
		Selected_Feature_Weight, Service_Feature_Name

"

andre
Constraint Violating Yak Guru

259 Posts

Posted - 2002-01-24 : 10:20:41

Instead of this:

WHERE   tmp.Selected_Feature_ParentID IN (#svcList#)

Use this:

WHERE CHARINDEX(','+CAST(tmp.Selected_Feature_ParentID AS varchar)+',',','+@svcList+',')>0

ToddV
Posting Yak Master

218 Posts

Posted - 2002-01-24 : 13:32:20

Brian,
I have put together this very simple example of a way you can do this. It comes with some warnings. For example, I have not really thought this all the way through.

1. You have to know the maximum number of list entries. In this case it is three. (You can overcome this with some dynamic code built with a loop. No time for that)

2. The Inner query employs a cross join to rank list items for each person. For large data sets that may not work out well.

Even with those concerns, you might be able to work this in:

Create table #Temp (
ID INT,
Col1 INT)

CREATE TABLE #TEMP2(
ID INT)

Insert #Temp Values (1,1)
Insert #Temp Values (1,5)
Insert #Temp Values (1,7)
Insert #Temp Values (2,14)
Insert #Temp Values (2,19)

Insert #Temp2 Values(1)
Insert #Temp2 Values(2)

SELECT C.ID ,
STRING
FROM (SELECT ID,
SUBSTRING(
MAX(CASE WHEN Rank = 1 THEN ',' + LTRIM(Col1) ELSE '' END)+
MAX(CASE WHEN Rank = 2 THEN ',' + LTRIM(Col1) ELSE '' END)+
MAX(CASE WHEN Rank = 3 THEN ',' + LTRIM(Col1) ELSE '' END),
2,10)AS "String"
FROM ( SELECT A.ID,A.Col1,Count(*) AS "rank"
FROM #TEMP A
JOIN #Temp B
ON A.ID = B.ID AND A.Col1>= B.COl1
GROUP BY A.ID,A.Col1 ) AS A
GROUP BY ID) AS B
JOIN #TEMP2 C
ON B.ID = C.ID

--BTW if the possible values of col1 we know up front, I could have skipped the whole ranking bit.

Edited by - toddv on 01/24/2002 13:33:49

perlpunk
Starting Member

2 Posts

Posted - 2002-01-24 : 23:29:09

First, Thanks for the help!
Next, here is what i came up with from your suggestion:

SELECT tmp.Selected_Feature_ParentID, tmp.Selected_Feature_FeatureID AS fid,
COUNT(tmp.Selected_Feature_FeatureID) AS rank
FROM dbo.Invite_Selected_Features tmp,
dbo.Invite_Selected_Features B
WHERE tmp.Selected_Feature_ParentID IN (23,24)
AND
tmp.Selected_Feature_FeatureID = B.Selected_Feature_FeatureID
AND
tmp.Selected_Feature_Key = 1
GROUP BY tmp.Selected_Feature_ParentID, tmp.Selected_Feature_FeatureID

This query works great to return the rows with the ID's that I need. The problem is the way the ranking part works when i use the rest of the query to put them all into a list it doesn't add some of the ID's to the list and it doubles some others up.

Here is what the above query returns.
ParentID fid rank
23 2 19
24 2 19
24 34 19
23 62 15
24 62 15
23 73 2
24 73 2
23 75 2
24 75 2
23 76 2
24 76 2
23 77 2
24 77 2

the 'fid' is what i need to apear in a list.
Example:
23 2,62,73,75,76,77
24 2,34,62,73,75,76,77

The don't have to apear in any particular order, they just need to be in the list. I will also make a couple other key points.
1: I have no idea how many rows will be returned by the above query
2: There can and probably will be more than two ParentID's returned

I will continue to search the forum to already posted material that might help me along, I just wanted to see there are any other ideas.

Thanks,

Brian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources