Use CTE to generate test data in SQL

user_2986153 0
Viewed 733

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
common-table-expression select postgresql sql
3 Answers

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
user_106767160

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
user_64361910 
WITH temp1 AS (
SELECT  'A' as grp ,1 as outcome
UNION 
SELECT  'A', 2
UNION
SELECT 'B',NULL
UNION 
SELECT 'B',1
) SELECT * FROM temp1
user_24557310
Related