Pivot Table

SELECT InterventionType.*, InterventionLocation.*, InterventionService.* FROM

(SELECT SURef, 
COALESCE([1],0) AS Phone,
COALESCE([2],0) AS [Assessment/Intervention],
COALESCE([3],0) AS Meeting,
COALESCE([4],0) AS Equipment,
COALESCE([5],0) AS [Admin],
COALESCE([6],0) AS Travel,

SUM(
COALESCE([1],0) + 
COALESCE([2],0) + 
COALESCE([3],0) + 
COALESCE([4],0) + 
COALESCE([5],0) + 
COALESCE([6],0)
) AS TotalInterventions
FROM
(
  SELECT SURef, InterventionType
  FROM [BOCClientIndex - SU Interventions]
 
) src
pivot
(
  COUNT(InterventionType)
  for InterventionType in ([1], [2], [3], [4], [5], [6])
) piv
GROUP BY SURef, 
COALESCE([1],0) ,
COALESCE([2],0) ,
COALESCE([3],0) ,
COALESCE([4],0) ,
COALESCE([5],0) ,
COALESCE([6],0) 
) AS InterventionType

INNER JOIN

(SELECT SURef, 
COALESCE([1],0) AS TeamBase,
COALESCE([2],0) AS [FamilyHomeRes],
COALESCE([3],0) AS Preschool,
COALESCE([4],0) AS School,
COALESCE([5],0) AS [DayService],
COALESCE([6],0) AS RespiteFamilyHomeShare,
COALESCE([7],0) AS [Other],
COALESCE([8],0) AS TeleHealth,


SUM(
COALESCE([1],0) + 
COALESCE([2],0) + 
COALESCE([3],0) + 
COALESCE([4],0) + 
COALESCE([5],0) + 
COALESCE([6],0) +
COALESCE([7],0) + 
COALESCE([8],0) 

) AS TotalInterventions

from 
(
  SELECT SURef, InterventionLocation
  FROM [BOCClientIndex - SU Interventions]
 
) src
pivot
(
  COUNT(InterventionLocation)
  FOR InterventionLocation in ([1], [2], [3], [4], [5], [6], [7], [8])
) piv
GROUP BY SURef, 
COALESCE([1],0) ,
COALESCE([2],0) ,
COALESCE([3],0) ,
COALESCE([4],0) ,
COALESCE([5],0) ,
COALESCE([6],0) ,
COALESCE([7],0) ,
COALESCE([8],0) 
) As InterventionLocation 

ON InterventionType.SURef = InterventionLocation.SURef

INNER JOIN

(
SELECT SURef, 
COALESCE([1],0) AS CommunityNursing,
COALESCE([2],0) AS OccupationalTherapy,
COALESCE([3],0) AS Paediatrics,
COALESCE([4],0) AS Physiotherapy,
COALESCE([5],0) AS Psychology,
COALESCE([6],0) AS PreSchoolLiaison,
COALESCE([7],0) AS SocialWork,
COALESCE([8],0) AS SpeechLanguage,
COALESCE([9],0) AS Psychiatry,
COALESCE([10],0) AS ClinicalNurse,
COALESCE([11],0) AS BehaviourSupport,
COALESCE([12],0) AS CommunityLinkWorker,
COALESCE([13],0) AS Psychotherapy,
COALESCE([14],0) AS AdvancedNursePractitioner,
COALESCE([15],0) AS AssistiveTechnology,

SUM(
COALESCE([1],0) + 
COALESCE([2],0) + 
COALESCE([3],0) + 
COALESCE([4],0) + 
COALESCE([5],0) + 
COALESCE([6],0) +
COALESCE([7],0) + 
COALESCE([8],0) +
COALESCE([9],0) + 
COALESCE([10],0) + 
COALESCE([11],0) + 
COALESCE([12],0) + 
COALESCE([13],0) + 
COALESCE([14],0) +
COALESCE([15],0) 

) AS TotalInterventions

from 
(
  select SURef, [Service]
  from [BOCClientIndex - SU Interventions]
 
) src
pivot
(
  COUNT([Service])
  FOR [Service] in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])
) piv
GROUP BY SURef, 
COALESCE([1],0) ,
COALESCE([2],0) ,
COALESCE([3],0) ,
COALESCE([4],0) ,
COALESCE([5],0) ,
COALESCE([6],0) ,
COALESCE([7],0) ,
COALESCE([8],0) ,
COALESCE([9],0) ,
COALESCE([10],0) ,
COALESCE([11],0) ,
COALESCE([12],0) ,
COALESCE([13],0) ,
COALESCE([14],0) ,
COALESCE([15],0) ) AS InterventionService

ON InterventionType.SURef = InterventionService.SURef


ORDER BY InterventionLocation.SURef