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

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

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

Code:

---

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

---

output:

Code:

---

Field1 Field2
1      2

---

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

Code:

---

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

---

output:

Code:

---

Field1 Field2
1      2
3      4
5      6

---