Is there a way to return more than 1 row in select without using existing tables

Question

Simple question, just out of curiosity.

For example select 1,2,3 that will show table with one column and three rows.

Something like this: select values(1),(2),(3)

*with one select statement

Answer 1

An example for my comment in your post.

DECLARE @TABLE TABLE (ONE INT, TWO INT, THREE INT)

INSERT INTO @TABLE VALUES (1,2,3)

SELECT UP.COL, UP.VALUE
FROM @TABLE
UNPIVOT (VALUE FOR COL IN (ONE,TWO,THREE)) UP

Answer 2

Query:

DECLARE @t TABLE (i1 INT, i2 INT, i3 INT)
INSERT INTO @t VALUES (1, 2, 3)

SELECT t.*
FROM @t
CROSS APPLY (
    VALUES(i1), (i2), (i3)
) t(value)

Output:

value
-----------
1
2
3

Additional info:

http://blog.devart.com/is-unpivot-the-best-way-for-converting-columns-into-rows.html