Q:

SQL how to pivot the whole table on the top left cell

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.
I would like to get the whole result table to pivot on the top left cell.
Right now, my query looks like this:
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 >= date(getdate()-7) and pid IS NOT NULL
group by pid

and it gives me this table
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

How do I pivot it so that I get a table that looks like this:
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

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.
Is it possible to do so?

As requested, here is a "sample" of the data and what my current query gives me from the sample.
Sample
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

Query result
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

Wanted result
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

A:

You can use UNION ALL to pivot it the way you want.
select 'cA' as pid
    , count(distinct case when ca is true and pid = 'r1' then id else null end) as r1
    , count(distinct case when ca is true and pid = 'r2' then id else null end) as r2
    , count(distinct case when ca is true and pid = 'r3' then id else null end) as r3
    , count(distinct case when ca is true and pid = 'r4' then id else null end) as r4
from table1
group by 1

union all

select 'cB' as pid
    , count(distinct case when cb is true and pid = 'r1' then id else null end) as r1
    , count(distinct case when cb is true and pid = 'r2' then id else null end) as r2
    , count(distinct case when cb is true and pid = 'r3' then id else null end) as r3
    , count(distinct case when cb is true and pid = 'r4' then id else null end) as r4
from table1
group by 1

union all

select 'cC' as pid
    , count(distinct case when cc is true and pid = 'r1' then id else null end) as r1
    , count(distinct case when cc is true and pid = 'r2' then id else null end) as r2
    , count(distinct case when cc is true and pid = 'r3' then id else null end) as r3
    , count(distinct case when cc is true and pid = 'r4' then id else null end) as r4
from table1
group by 1

union all

select 'cD' as pid
    , count(distinct case when cd is true and pid = 'r1' then id else null end) as r1
    , count(distinct case when cd is true and pid = 'r2' then id else null end) as r2
    , count(distinct case when cd is true and pid = 'r3' then id else null end) as r3
    , count(distinct case when cd is true and pid = 'r4' then id else null end) as r4
from table1
group by 1

order by 1

Feel free to add the WHERE clause as necessary. 
Keep in mind that if there are more pids (r5,r6 ,etc), they need to be manually added. It's not very efficient but I don't think there is another way in Redshift.

