[HowTo] Generate Multiple Rows, Single SELECT (T-SQL)

[Dave Sell]

Question: How to generate multiple rows of pseudo-data using a single SELECT statement?

Background: SELECTing multiple fields of pseudo-data is easy:

SELECT 1 AS [Field1], 2 as [Field2]

output:

Field1 Field2
1       2

In order to generate multiple rows we can use the UNION keyword:

SELECT 1 AS [Field1], 2 as [Field2] UNION
SELECT 3 AS [Field1], 4 as [Field2] UNION
SELECT 5 AS [Field1], 6 as [Field2]

output:

Field1 Field2
1       2
3       4
5       6