Disaggregate count by column content after group by

user_2651069 0
Viewed 61

I've got the following table in SQL Server:

ExperienceId EmployeeId ExperienceType
1 1 'Work'
2 1 'Internal'
3 2 'Internal'
4 3 'External'
5 3 'External'

There are only 3 possible values of ExperienceType: 'Work', 'Internal' and 'External'

I'd like to know how many experiences of each type has each employee:

EmployeeId Work exp Internal exp External exp
1 1 1 0
2 0 1 0
3 0 0 2

I've tried this

select EmployeeId, count(*)
from EmployeeExperiences
group by EmployeeId

But that only gives me the total number of experiences per employee, and I don't know how to disaggregate that by ExperienceType.

group-by sql-server sql
2 Answers

One approach is to use conditional aggregation (CASE WHEN inside the aggregation function).

select 
  employeeid,
  count(case when experiencetype = 'Work' then 1 end) as work_exp,
  count(case when experiencetype = 'Internal' then 1 end) as internal_exp,
  count(case when experiencetype = 'External' then 1 end) as external_exp
from employeeexperiences
group by employeeid
order by employeeid;

Another option is to use the PIVOT clause.

user_22707620

Result with PIVOT:

select EmployeeId, [Work], [Internal], [External] from (
  select EmployeeId, ExperienceType from EmployeeExperiences) as SourceTable
  pivot (
    count(ExperienceType ) for ExperienceType in ([Work], [Internal], [External])) as 
  PivotTable
user_177001550
Related