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




Corey

I Has Returned!!
Go to Top of Page

rickincanada
Starting Member

18 Posts

Posted - 2011-03-31 : 11:06:52
Disregard, I've found my way through this.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-31 : 11:13:01
Why don't you just do a JOIN or EXISTS query?
Go to Top of Page

rickincanada
Starting Member

18 Posts

Posted - 2011-03-31 : 11:22:29
Disregard. I've found my way through this.
Go to Top of Page

rickincanada
Starting Member

18 Posts

Posted - 2011-03-31 : 11:46:03
Thank you all for your help on this!
Go to Top of Page
   

- Advertisement -