Disaggregate count by column content after group by

Solution 1:

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.

Solution 2:

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