Return Multiple Row Values in single Value

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 Administration

Return Multiple Row Values in single Value

Author

Topic

rickincanada
Starting Member

18 Posts

Posted - 2011-03-30 : 10:29:20

I have a need to return multiple row values to later be used within a WHERE clause. I'm sure this is possible however I need some help to understand how to do it. Here is a simple example of my tables and SELECT.

id link_id security_code
1 1 1
2 1 2
3 2 1
4 2 2
5 2 3
6 3 1
7 3 2
8 3 3

I need to SELECT DISTINCT the security_code values in a format that can be used within an IN clause later on in the same statement. An example here would be:

SELECT DISTINCT (security_code) FROM table WHERE link_id = '2' AS ''security_filter'

I would want to get back something along the lines of (1, 2, 3) so that later on in the WHERE clause of my main SELECT I can write something to the effect of: WHERE security_code IN 'security_filter'. I'm not sure if this can be done all in one statement however I'm hoping so. Thanks for any help you can provide!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-03-30 : 10:36:33

EEK!

quote:

I would want to get back something along the lines of (1, 2, 3) so that later on in the WHERE clause of my main SELECT I can write something to the effect of: WHERE security_code IN 'security_filter'. I'm not sure if this can be done all in one statement however I'm hoping so. Thanks for any help you can provide!

that would not be valid syntax...

you would need to do something more like


Where securityFilter like +'%,'+convert(varchar,security_code)+',%'

i think this will do it...


	Select
		link_id,
		security_filter = ',' + security_code
	From (Select distinct link_id from table) Z
	Cross Apply
	(
		Select
			Cast(security_code As varchar) + ', '
		From yourTable A
		Where Z.link_id = A.link_id
		Group By A.link_id, A.security_code
		For Xml Path('')
	) Y (security_code)
	Group By link_id, Projects