Is it possible to have a tableless select with mul

Question

A SELECT without a FROM clause gets us a multiple columns without querying a table:

SELECT 17+23, REPLACE('bannanna', 'nn', 'n'), RAND(), CURRENT_TIMESTAMP;

How can we write a query that results in multiple rows without referring to a table? Basically, abuse SELECT to turn it into a data definition statement. The result could have a single column or multiple columns.

I'm most interested in a DBMS neutral answer, but others (e.g. based on UNPIVOT) are welcome. I'd like to collect as many ways of doing this as possible. There's no technique application behind this question; it's more theoretical than practical.

Answer 1

T-SQL's UNPIVOT can transpose data from rows to columns. Multiple unpivots is equivalent to the Cartesian product of each unpivoted column.

SELECT N, S
  FROM (SELECT 1 aa, 2 ab, 3 ac, 'a' ba, 'b' bb, 'c' bc) s
  UNPIVOT (N for Num in (aa,ab,ac)) AS t
  UNPIVOT (S for Str in (ba,bb,bc)) AS u

Result:

N | S
--+--
1 | a
1 | b
1 | c
2 | a
2 | b
2 | c
3 | a
3 | b
3 | c

Answer 2

Use a UNION:

SELECT 1
UNION
SELECT 2

Looks like this in MySQL:

+---+
| 1 |
+---+
| 1 | 
| 2 | 
+---+

Use UNION ALL to avoid the loss of non-unique rows.

Answer 3

You can use Table Value Constructors for this if supported by your RDBMS. Here's an example from Mr Celko

SELECT X.*
FROM   (VALUES (1, 3.07766, 6.31371, 12.7062, 63.65600),
               (2, 1.88562, 2.91999, 4.30265, 9.92482),
               (3, 1.63774, 2.35336, 3.18243, 5.84089)) AS X (A, B, C, D, E);