Sql Select from List

R

rgratto2

2[H]4U
Joined
Jul 22, 2005
Messages
2,319
I feel like I may have seen something like this around but my google fu is weak. Is there any way to do a select from a user defined list of data? Such as:

Code: 
Select * from ('Value1','Value2')
 
Let me see how rusty my SQL is:
Code: 
SELECT * FROM myTable WHERE myField IN ('myFirstValue','mySecondValue')
 
Or...


Code: 
SELECT * FROM myTable WHERE 'myValue' IN (myFieldName1, myFieldName2, myFieldName3)

i.e.,


Code: 
SELECT * FROM ADDRESS WHERE '123 Main Street' IN (ADDRESS1, ADDRESS2, ADDRESS3)
 
I'm not sure what you're trying to do. What do the values in your user defined list correspond to? I don't see the point in selecting a value from just a list of values, since the answer is just the value that you input? (if that makes sense).
 
My guess is that he meant defining some data in the query itself, basically selecting from a temp table.

Something like this:
Code: 
select *
from ( 	select 'test1','123' as test
	union select 'test2','234' as test
	union select 'test3','345' as test
	union select 'test4','456' as test
) as t1;

Output:
Code: 
test1,123
test2,234
test3,345
test4,456
 
Sgraffite said:
My guess is that he meant defining some data in the query itself, basically selecting from a temp table.

Something like this:
Code: 
select *
from ( 	select 'test1','123' as test
	union select 'test2','234' as test
	union select 'test3','345' as test
	union select 'test4','456' as test
) as t1;

Output:
Code: 
test1,123
test2,234
test3,345
test4,456
Click to expand...

This is what im trying to do. I think I may be able to get this to work after playing around with it a bit, thanks!
 
S U C C E S S, thats the way you spell success! Rockin - didnt even take that long.
 
This works too:

Code: 
select  'Value1' as col1 into #temp 
insert into #temp values( 'Value2')
insert into #temp values( 'Value3')
insert into #temp values( 'Value4')
insert into #temp values( 'Value5')
insert into #temp values( 'Value6')

select * from #temp
 
Pwyl_The_Destroyer said:
This works too:

Code: 
select  'Value1' as col1 into #temp 
insert into #temp values( 'Value2')
insert into #temp values( 'Value3')
insert into #temp values( 'Value4')
insert into #temp values( 'Value5')
insert into #temp values( 'Value6')

select * from #temp
Click to expand...

You could combine Pwyl_The_Destroyer's and Sgraffite's solutions by creating a SQL function that can parse a comma-delimited string of values and return it as a query table. It might make your code look a little cleaner when executing it. For example, you could do:

Code: 
SELECT * FROM ParseStringToTable('Value1,Value2,Value3')

that would return:

Code: 
Value1
Value2
Value3
 
You must log in or register to reply here.
Back
Top