sql - 在sqldf/R中的情况

Question

如果我有DF

   A B  Col
    1 1  A
    2 2  B
    1 2  C
    2 1  D
    1 3  E
    2 3  F

我尝试如下使用sqldf

 Test <- sqldf("                SELECT A,
                                    case when A = '1' and B = '1' then Col else NULL end as Test_1, 
                                    case when A = '1' and B = '2' then Col else NULL end as Test_2, 
                                    case when A = '1' and B = '3' then Col else NULL end as Test_3, 
                                    case when A = '2' and B = '1' then Col else NULL end as Test_4, 
                                     case when A = '2' and B = '2' then Col else NULL end as Test_5, 
                                      case when A = '2' and B = '3' then Col else NULL end as Test_6 

                                   FROM  DF
                                   group by A;")

但是，它仅针对每种情况A和其他列NULL返回结果，因此不是我想要的结果

A Test1 Test2 Test3 Test4 Test5 Test6
1   A     C     E    Null  NULL  NULL
2  NULL  NULL  NULL   D      B    F

每个A我只会得到一个结果

A Test1 Test2 Test3 Test4 Test5 Test6
1   G    NULL  NULL  Null  NULL  NULL
2  NULL  NULL  NULL  NULL   B    NULL

我在做什么错，有没有办法获取所需的格式？

Answer 1

在SQL中称为条件聚集（通常用于数据透视），因为@Gregor注释只是围绕MAX()语句运行像MIN()的聚集（即使CASE都可以工作）：

SELECT A,
       MAX(CASE WHEN A = '1' AND B = '1' THEN Col ELSE NULL END) as Test_1, 
       MAX(CASE WHEN A = '1' AND B = '2' THEN Col ELSE NULL END) as Test_2, 
       MAX(CASE WHEN A = '1' AND B = '3' THEN Col ELSE NULL END) as Test_3, 
       MAX(CASE WHEN A = '2' AND B = '1' THEN Col ELSE NULL END) as Test_4, 
       MAX(CASE WHEN A = '2' AND B = '2' THEN Col ELSE NULL END) as Test_5, 
       MAX(CASE WHEN A = '2' AND B = '3' THEN Col ELSE NULL END) as Test_6
FROM DF
GROUP BY A