Create table where each column is a distinct value retrieved from select query

Question

I want to create a table where each column is a distinct value retrieved from select query.

Example:

[Query]

SELECT DISTINCT col 
FROM table

[Result]

col
 --- 
val1
val2
val3
val4

Requested table:

Column1 | Column2 | Column3 | ... | ColumnN
-------------------------------------------
 val1   |  val2   |  val3   | ... | valN

There is an unknown number of distinct values. All columns should be create as type TEXT.

Is this possible using SQL without procedures?

Thanks.

If "procedures" means dynamic sql/front end code/building an sql string and executing it etc, then the answer is No — Caius Jard, Oct 15 '18 at 11:48
Possible duplicate of [Looping for each column name of a MSSQL table using php](https://stackoverflow.com/questions/45307487/looping-for-each-column-name-of-a-mssql-table-using-php) — JeffUK, Oct 15 '18 at 12:26

klin · Accepted Answer · 2018-10-15T12:02:03.587

You have to use a dynamic command. If you do not want to create a function, use an anonymous code block, example:

create table table_cols(col text);
insert into table_cols values
('col1'), 
('col2'),
('col3');

do $$
begin
    execute format('create table new_table(%s text)', string_agg(distinct col, ' text, '))
    from table_cols;
end
$$

Check:

\d new_table

            Table "public.new_table"
 Column | Type | Collation | Nullable | Default 
--------+------+-----------+----------+---------
 col1   | text |           |          | 
 col2   | text |           |          | 
 col3   | text |           |          |

Create table where each column is a distinct value retrieved from select query

1 Answers1