如何让虚拟变量显示在一行中

Question

我的桌子是这样的：

0    Number Certification
1    400009           CSR
2    400009            MD
3    400009           MHP
4    400032           CSP
5    400032           MH

在我使用get dummies之后，那么桌子是这样的：

 Number  Certification_ACD  ...  Certification_TSC  Certification_TSE
1    400009                  0  ...                  0                  0
2    400009                  0  ...                  0                  0
3    400009                  0  ...                  0                  0

我希望数字列只显示一次。有人能帮忙吗？谢谢！

Answer 1

如果知道它们的数量，您可以执行以下操作：

select
  number,
  sum(case when certification = 'ACD' then 1 else 0 end) as cert_asd,
  sum(case when certification = 'TSC' then 1 else 0 end) as cert_tsc,
  sum(case when certification = 'TSE' then 1 else 0 end) as cert_tse,
  -- other cases here
from t
group by number

sum()函数可添加认证。如果一个人多次拥有相同的认证，它将显示2，或3等。

Answer 2

以下查询可以满足您的需要：

SELECT
    Number,
    SUM(Certification='ACD') AS Certification_ACD,
    SUM(Certification='TSC') AS Certification_TSC,
    SUM(Certification='TSE') AS Certification_TSE
FROM
    YourTable
GROUP BY
    Number
ORDER BY
    Number;