Use CTE to generate test data in SQL

Question

I am trying to use a CTE to generate a data table for a unit test in SQL (postgresql).

WITH temp1 AS (
SELECT
  ('A', 'A', 'B', 'B') AS grp,
  (1, 2, NULL, 1) AS outcome
)

SELECT * 
FROM temp1

The above query is generating a single row rather than a 4-row table that would be useful to my unit test. How can I generate the 4-row table in the form:

grp.....outcome
A.......1
A.......2
B.......NULL
B.......1

Answer 1

You could just use the values() syntax to create the rows, like so:

with temp1(grp, outcome) as (values ('A', 1), ('A', 2), ('B', null), ('B', 1))
select * from temp1

Demo on DB Fiddle:

grp | outcome
:-- | ------:
A   |       1
A   |       2
B   |    null
B   |       1

Answer 2

You don't need a CTE. Use UNION ALL, as in:

select 'A' as grp, 1 as outcome
union all select 'A', 2
union all select 'B', null
union all select 'B', 1