Is it possible to have a tableless select with multiple rows?

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

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 2

I'm not sure how many RDBMSs actually support this syntax (Edit Just tested this and works fine in SQL Server 2008) but you can use Row Constructors for this. Here's an example from Joe Celko

SELECT X.*

  FROM (VALUES (CAST (1 AS INTEGER), 

           CAST (3.07766 AS FLOAT), 

           CAST (6.31371 AS FLOAT), 

           CAST (12.7062 AS FLOAT), 

           CAST (63.65600 AS FLOAT)), 

 (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);