title: Redshift. How can we transpose (dynamically) a table from columns to rows?
body: <p>How can we transpose a Redshift table from columns to rows?</p>
  
  <p>For example, if we have a generic (not already known) table like the following:</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>How we can achieve the following result?</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>Where the target table, the number of columns (alfa, beta, gamma, ..., omega) are all dynamic (so We're looking for a solution that no <code>case</code> <code>when</code> mapping for each column is needed, since We'd like to apply this to several different tables).</p>
  
  <p>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).</p>
  
  <p>Our Redshift version is:</p>
  
  <p><code>PostgreSQL 8.0.2, Redshift 1.0.3380</code></p>
  
  <p>How can we do that?</p>