title: SQL how to pivot the whole table on the top left cell
body: <p>After googling it for a while, I have figured that I cannot use "pivot" as it is not available on Redshift but I thought that maybe there are geniuses around here that might be able to help me out on this one.</p>
  
  <p>I would like to get the whole result table to pivot on the top left cell.</p>
  
  <p>Right now, my query looks like this:</p>
  
  <pre><code>SELECT pid,
  COUNT(DISTINCT CASE WHEN colA = 'true' THEN id ELSE NULL END) as "cA",
  COUNT(DISTINCT CASE WHEN colB = 'true' THEN id ELSE NULL END) as "cB",
  COUNT(DISTINCT CASE WHEN colC = 'true' THEN id ELSE NULL END) as "cC",
  COUNT(DISTINCT CASE WHEN colD = 'true' THEN id ELSE NULL END) as "cD",
  FROM Data1.Table1 
  WHERE date1 &gt;= date(getdate()-7) and pid IS NOT NULL
  group by pid
  </code></pre>
  
  <p>and it gives me this table</p>
  
  <pre><code>pid cA  cB  cC  cD
  r1  A1  B1  C1  D1
  r2  A2  B2  C2  D2
  r3  A3  B3  C3  D3
  r4  A4  B4  C4  D4
  </code></pre>
  
  <p>How do I pivot it so that I get a table that looks like this:</p>
  
  <pre><code>pid r1  r2  r3  r4
  cA  A1  A2  A3  A4
  cB  B1  B2  B3  B4
  cC  C1  C2  C3  C4
  cD  D1  D2  D3  D4
  </code></pre>
  
  <p>In reality, the original table as many more columns than this example and is not easy to read. That is why I would like to pivot it and make it mostly vertical.</p>
  
  <p>Is it possible to do so?</p>
  
  <hr>
  
  <p>As requested, here is a "sample" of the data and what my current query gives me from the sample.</p>
  
  <p>Sample</p>
  
  <pre><code>id      date1       pid cA      cB      cC      cD
  1000    9/6/2017    r1  true    false   true    false
  1001    9/7/2017    r1  false   true    true    true
  1001    9/5/2017    r3  true    false   true    false
  1002    9/7/2017    r2  true    true    true    false
  1003    9/1/2017    r4  false   false   false   true
  1004    8/24/2017   r3  true    false   true    false
  1002    9/7/2017    r2  false   false   true    false
  </code></pre>
  
  <p>Query result</p>
  
  <pre><code>pid cA  cB  cC  cD
  r1  1   1   2   1
  r2  1   1   2   1
  r3  2   0   2   0
  r4  0   0   0   0
  </code></pre>
  
  <p>Wanted result</p>
  
  <pre><code>pid r1  r2  r3  r4
  cA  1   1   2   0
  cB  1   1   0   0
  cC  2   2   2   0
  cD  1   1   0   0
  </code></pre>