Disaggregate count by column content after group by-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

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