3

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?

GarouDan
  • 3,743
  • 9
  • 49
  • 75
  • I have had the same exact problem except source data was coming from an API, ended up creating a python script to pivot or transpose the data set. – demircioglu Sep 04 '18 at 22:04
  • Hum. Maybe is possible to create a python function to create some kind of dynamic query. But I don't know if it will be possible to the result using something like `select * from pivot_function('schema_name', 'table_name', 'table_id_column_name');` – GarouDan Sep 04 '18 at 22:32
  • Redshift UDFs cannot access table data. – John Rotenstein Sep 04 '18 at 23:42

3 Answers3

3

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
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
0

use the unpivot operation in redshift

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

Akash Singh
  • 171
  • 1
  • 10
0

Now Redshift supports Pivot and Un-pivot operations,You can use these operations to get the desired output as per this use case you have mentioned. Please check the below link to know more details. https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause-pivot-unpivot-examples.html

Srikant
  • 406
  • 4
  • 11