sql - 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

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