Is it possible to SELECT multiple constants into multiple resultset rows in SQL?

Question

I know I can "SELECT 5 AS foo" and get the resultset:

foo

5

(1 row)

...is there any way to "SELECT 5,6,7 AS foo" and get the resultset:

foo

5

6

7

(3 rows)

...I'm well aware that this is not typical DB usage, and any conceivable usage of this is probably better off going w/ a more ordinary technique. More of a technical question.

Note: I know I could use a big gross list of UNIONs -- I'm trying to find something else.

Answer 1

this is easy with a number table, here is an example

select number as foo
 from master..spt_values
where type = 'p'
and number between 5 and 7

or if you want to use in

select number as foo
 from master..spt_values
where type = 'p'
and number in(5,6,7)

Answer 2

select foo
from (select 1 as n1, 2 as n2, 3 as n3) bar
unpivot (foo for x in (n1, n2, n3)) baz;

Answer 3

It's possible using these and other techniques (as anyone who has interviewed for a database developer's position will tell you). But it's usually easier (and the tools are more appropriate) to do this in another abstraction layer; i.e. your DAL, or beyond, where you view the data as a list of some kind. Although rdbms products provide facilitators, it's a distortion of the relational conceptual model.

Answer 4

Just for fun, wouldn't dream of using it for real:

WITH numbers AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY name) AS 'RowNumber'
    FROM sys.all_objects
) 
SELECT RowNumber 
FROM numbers 
WHERE RowNumber BETWEEN 5 AND 7;