Creating a table with values from a WITH Query-CodePudding

i want to create a table with specific values.

Something like this:

SELECT * FROM (VALUES ('Data1', 'one'), ('Data2', 'two') ) AS t ("ColumnName1","ColumnName2");

But instead of using manual values, i want to use values from a WITH QUERY

Like this:

WITH Test AS 
(
Select table1.id FROM table1
)

SELECT * FROM (VALUES ('Data1', Test.id), ('Data2', Test.id) ) AS t ("ColumnName1","ColumnName2");

How do I access the data from the WITH query?

CodePudding user response：

You can join lateral to the CTE

create table Table1 (
id varchar(30)
)

insert into Table1 values ('one'), ('two'), ('three'), ('party')

with CTE_IDLIST AS (
 select id
 , row_number() over (order by null) as rn
 from Table1
) 
select q.*
from CTE_IDLIST list
cross join lateral (values ('Data'||list.rn, list.id)) as q("ColumnName1","ColumnName2")

ColumnName1 | ColumnName2
:---------- | :----------
Data1       | one        
Data2       | two        
Data3       | three      
Data4       | party

db<>fiddle here

CodePudding user response：

You use like any other table

CREATE TABLE table1 (id varchar(4))

INSERT INTO Table1 VALUES ('one'),('two')

WITH Test AS 
(
Select table1.id FROM table1
)
SELECT t1."ColumnName1" ,test.id as "ColumnName2" FROM test CROSS JOIN (SELECT 'Data1' AS "ColumnName1"
UNION SELECT 'Data2') t1;

ColumnName1 | ColumnName2
:---------- | :----------
Data1       | one        
Data2       | one        
Data1       | two        
Data2       | two

db<>fiddle here