Is there a way to return more than 1 row when selecting without using existing tables

Question

A simple question, just out of curiosity.

For example select 1,2,3, which will show tablewith one columnand three rows.

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

* with one select statement

Answer 1

As it turned out, there is a simple code that I was looking for:
select n from (values (1),(2),(3)) D(c);

Answer 2

An example of 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 3

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)

:

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

:

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