SQL如何在左上角的单元格上转动整个表格

Question

Google搜索一段时间后，我发现我不能使用“pivot”，因为它在Redshift上不可用，但我认为也许这里有天才也许能帮助我解决这个问题。

我想让整个结果表在左上角的单元格上转动。

现在，我的查询看起来是这样的：

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 

，这让我这个表

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 

我怎么转动它让我得到一个表，看起来像这样：

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 

实际上，原来的表格比这个例子多了很多列，并且不容易阅读。这就是为什么我想旋转它并使其大部分垂直。

有没有可能这样做？

---

按照要求，这里有一个数据，什么我当前的查询给我从样品的“样品”。这里

我用纯支点转换：

样品

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 

查询结果

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 

通缉的结果

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 

Answer 1

您可以使用UNION ALL以您想要的方式旋转它。

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 

根据需要随意添加WHERE子句。

请记住，如果有更多的pid（r5，r6等），他们需要手动添加。这不是很有效率，但我认为Redshift没有其他办法。

Answer 2

试试这个

CREATE TABLE temp_table (ids INT,A NVARCHAR(5),B NVARCHAR(5),C NVARCHAR(5),D NVARCHAR(5)) 
INSERT INTO temp_table VALUES (1,'A1','B1','C1','D1'), (2,'A2','B2','C2','D2'), (3,'A3','B3','C3','D3'),(4,'A4','B4','C4','D4'),(5,'A5','B5','C5','D5') 
SELECT * FROM temp_table 

DECLARE @temp_table_columns TABLE (id INT,col NVARCHAR(20)) 
DECLARE @result_val TABLE(id INT,value NVARCHAR(max)) 
INSERT INTO @temp_table_columns SELECT ORDINAL_POSITION id,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'temp_table' 
DECLARE @initial_value NVARCHAR(MAX)='',@starter INT = (SELECT MAX(id) FROM @temp_table_columns),@initial INT = 0 
WHILE(@initial<@starter) 
BEGIN 
DECLARE @rower NVARCHAR(20) = (SELECT col FROM @temp_table_columns WHERE [email protected]+1) 
INSERT INTO @result_val EXEC ('SELECT '''[email protected]+''', '[email protected]+' FROM temp_table') 
SET @initial_value = @initial_value + 
' UNION SELECT '+CONVERT(NVARCHAR,@initial)+' [id],'''[email protected]+''' '[email protected] +(SELECT ', '''+value+''' ['+value+']' 
FROM @result_val WHERE [email protected] FOR XML PATH('')) 
SET @[email protected]+1 
END 
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='temp_table') DROP TABLE temp_table 
SET @initial_value = 'SELECT * INTO temp_table FROM (' + SUBSTRING(@initial_value,8,LEN(@initial_value)) + ') A WHERE id<>0' 
EXEC (@initial_value) 
ALTER TABLE temp_table DROP COLUMN [id] 
SELECT * FROM temp_table 

您可以相应地修改它。 :)