ID       PROVIDER_CDE     GRF_COUNT   ACU_COUNT   
100034     GRF               4              1          
100054     GRF               3              1

SELECT Distinct
a.id, a.PROVIDER_CDE,  a.GRF_COUNT, E.ACU_COUNT       

FROM
(SELECT *
FROM
(SELECT id, PROVIDER_CDE, count(*) GRF_COUNT
FROM enrolment
WHERE Provider_cde = 'GRF'


GROUP BY id, PROVIDER_CDE))A

INNER JOIN
(SELECT *
FROM(
SELECT ID,PROVIDER_CDE, count(*) ACU_COUNT
FROM enrolment 
WHERE Provider_cde = 'ACU'
GROUP ID, PROVIDER_CDE))E
ON ID = ID
ORDER BY ID ASC

ID     PROVIDER_CDE   GRF COUNT   ACU_COUNT    CLM_COUNT
100034  GRF               4              1            0
100054  GRF               3              1            0

SELECT 	ID, PROVIDER_CDE,
	COUNT(CASE WHEN GRF_COUNT = 'GRF' THEN 1 END) as GRF_COUNT,
	COUNT(CASE WHEN GRF_COUNT = 'ACU' THEN 1 END) as ACU_COUNT,
        COUNT(*) as CLM_COUNT
FROM	enrolment
WHERE	PROVIDER_CDE	IN ('GRF', 'ACU')
GROUP BY ID, PROVIDER_CDE