Redshift. How can we transpose (dynamically) a table from columns to rows?

user_727184 0
Viewed 13397

How can we transpose a Redshift table from columns to rows?

For example, if we have a generic (not already known) table like the following:

source table:

date        id      alfa                beta                gamma   ...                 omega
2018-08-03  1       1                   2                   3                           4
2018-08-03  2       4                   3                   2                           1
...
2018-09-04  1       3                   1                   2                           4
...

How we can achieve the following result?

transposed table:

date        id      column_name     column_value
2018-08-03  1       alfa            1
2018-08-03  1       beta            2
...
2018-08-03  2       omega           1
...
2018-09-04  1       gamma           2
...

Where the target table, the number of columns (alfa, beta, gamma, ..., omega) are all dynamic (so We're looking for a solution that no case when mapping for each column is needed, since We'd like to apply this to several different tables).

But we will have and date and id fields in all target tables (or at last a primary key or a candidate key in all tables).

Our Redshift version is:

PostgreSQL 8.0.2, Redshift 1.0.3380

How can we do that?

amazon-redshift multiple-columns transpose pivot-table dynamic
3 Answers

You would need to hard-code the column names into the query.

CREATE TABLE stack(date TEXT, id BIGINT, alpha INT, beta INT, gamma INT, omega INT);

INSERT INTO STACK VALUES('2018-08-03', 1, 1, 2, 3, 4);
INSERT INTO STACK VALUES('2018-08-03', 2, 4, 3, 2, 1);
INSERT INTO STACK VALUES('2018-08-04', 1, 3, 1, 2, 4);

SELECT
  date,
  id,
  col,
  col_value
FROM
(
SELECT date, id, alpha AS col_value, 'alpha' AS col FROM stack
UNION
SELECT date, id, beta  AS col_value, 'beta'  AS col FROM stack
UNION
SELECT date, id, gamma AS col_value, 'gamma' AS col FROM stack
UNION
SELECT date, id, omega AS col_value, 'omega' AS col FROM stack
) AS data
ORDER BY date, id, col

Results in:

2018-08-03  1   alpha   1
2018-08-03  1   beta    2
2018-08-03  1   gamma   3
2018-08-03  1   omega   4
2018-08-03  2   alpha   4
2018-08-03  2   beta    3
2018-08-03  2   gamma   2
2018-08-03  2   omega   1
2018-08-04  1   alpha   3
2018-08-04  1   beta    1
2018-08-04  1   gamma   2
2018-08-04  1   omega   4
user_1747770

use the unpivot operation in redshift

select date,id,column_name,col from table_name unpivot (col for column_name in (alpha,beta,gamma,.....))

user_100020130
user_68572310
Related