I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns.
Example input:
CREATE TABLE tbl_wide (
p_key integer PRIMARY KEY,
col_a integer,
col_b integer,
col_c integer);
INSERT INTO tbl_wide (p_key, col_a, col_b, col_c)
VALUES (1,10,20,30),(2,50,10,5);
p_key | col_a | col_b | col_c |
---|---|---|---|
1 | 10 | 20 | 30 |
2 | 50 | 10 | 5 |
Desired output:
f_key | col | val |
---|---|---|
1 | a | 10 |
1 | b | 20 |
1 | c | 30 |
2 | a | 50 |
2 | b | 10 |
2 | c | 5 |
Unnesting the arrays produces the correct result:
SELECT p_key as f_key,
unnest(array['a','b','c']) col,
unnest(array[col_a, col_b, col_c]) val
FROM tbl_wide
However, given the number of columns and tables to convert I would like to use a reference table:
CREATE TABLE tbl_reference (
col_identifier text primary key,
list_of_cols text);
INSERT INTO tbl_reference (col_identifier, list_of_cols)
VALUES ('a','col_a'),('b','col_b'),('c','col_c');
col_identifier | list_of_cols |
---|---|
a | col_a |
b | col_b |
c | col_c |
So my query becomes something like:
SELECT p_key as f_key,
unnest((SELECT array_agg(col_identifier) FROM tbl_reference)) col,
unnest((SELECT array_agg(list_of_cols) FROM tbl_reference)) val
FROM tbl_wide
However, I can't figure out how to unnest the column values. The column names as strings are returned instead:
f_key | col | val |
---|---|---|
1 | a | col_a |
1 | b | col_b |
1 | c | col_c |
2 | a | col_a |
2 | b | col_b |
2 | c | col_c |
The following works but I the point is I want to avoid having to write out the column names each time:
SELECT p_key as f_key,
unnest((SELECT array_agg(col_identifier) FROM tbl_reference)) col,
unnest(array[col_a, col_b, col_c]) val
FROM tbl_wide