0

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

1 Answer 1

0

Figured it out:

SELECT p_key AS f_key, col_identifier AS col, val::int
FROM tbl_wide
  CROSS JOIN LATERAL jsonb_each_text(to_jsonb(tbl_wide) - 'p_key') AS un(col_name, val)
  JOIN tbl_reference ON col_name = list_of_cols
  ORDER BY f_key, col;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.