how to let dummy variables show in one line

user_11001783 0
Viewed 52

My table is like this:

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

After I use get dummies, then the table is like this:

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

I want the number column shows only once. Can anyone help? Thanks!

sql
2 Answers

The following query should serve your needs:

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;
user_154787260

If the number of them is known you can do:

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

The SUM() function adds the certifications. If a person has the same certification multiple times, it will show 2, or 3, etc.

user_64361910
Related