title: pivot rows to columns in redshift dynamically
body: <p>I have a Redshift table with different cases, company and date(timestamp)</p>
  
  <p>I used the following query to aggregate number of cases per company by month:</p>
  
  <pre><code>      SELECT
      DATE_TRUNC('MONTH', case_date) AS month,
      company_id,
      COUNT(DISTINCT case_number)      AS case_count
    FROM case
    WHERE case_date &gt;= '2017-01-01'
    AND case_date &lt; DATE_TRUNC('MONTH', CURRENT_DATE)
    GROUP BY 1, 2
    ORDER BY 1
  </code></pre>
  
  <p>And got: </p>
  
  <pre><code>month                  company_id       case_count
  2017-01-01 00:00:00     5786            4
  2017-01-01 00:00:00     8681            1
  .........               ....         .....
  2018-08-01              ....         .....      
  </code></pre>
  
  <p>I want to pivot the table by company. The desired output is to have each company on a row and columns are each month from 2017-01-01 to the previous month of the current date. So it will look like: </p>
  
  <pre><code>  company_id   2017-01-01_case_count  2017-02-01_case_count  .. 2018-08-01..
      5786          4                        7
  </code></pre>
  
  <p>I checked many solutions were using CASE, but I think it's not a solution for me since I have so many different months. I also want the solution to be dynamic so that later on I don't have to come back and change my query as time goes by.</p>
  
  <p>I can certainly dump all the data into Python and do this but I want to solve the problem within Redshift.</p>