Cannot get Pivot to work in SQL Server

Question

I have a query in SQL server I need to pivot. I built the code to pivot but I am getting errors (Incorrect syntax near the keyword 'distinct').

Here is code that is working:

select count(distinct patient_id) as 'Consumer Count',SERVICE_CAT,FISCAL_YEAR,Age_group_1 AGE_GROUP
from #CHECK_TEMP_MH
where SERVICE_CAT <> 'Applied Behaviorial Analysis Services'
and SERVICE_CAT <> 'SUD Labs'
and Age_group_1 <> 'Unknowm'
group by SERVICE_CAT,FISCAL_YEAR,Age_group_1

Here are the results:

Here is the code for pivoting the results:

select * from
(
select count(distinct patient_id),SERVICE_CAT,FISCAL_YEAR,Age_group_1 AGE_GROUP
from #CHECK_TEMP_MH
where SERVICE_CAT <> 'Applied Behaviorial Analysis Services'
and SERVICE_CAT <> 'SUD Labs'
and Age_group_1 <> 'Unknowm'
) t
pivot
(
count(distinct patient_id)
for Age_group_1 in ([SED_0-5],[SED_6-12],[SED_13-17],[SMI_18-21],[SMI_22-64],[SMI_65+])
) as Pvt
order by fiscal_year

What am I doing wrong?

Answer 1

select SERVICE_CAT, FISCAL_YEAR, [SED_0-5], [SED_6-12], [SED_13-17], [SMI_18-21], [SMI_22-64], [SMI_65+] from
(
select count(distinct patient_id) As PatientIDCount,SERVICE_CAT,FISCAL_YEAR,Age_group_1 AGE_GROUP
from #CHECK_TEMP_MH
where SERVICE_CAT <> 'Applied Behaviorial Analysis Services'
and SERVICE_CAT <> 'SUD Labs'
and Age_group_1 <> 'Unknowm'
group by SERVICE_CAT,FISCAL_YEAR,Age_group_1) t
pivot
(
Max(PatientIdCount)
for Age_group in ([SED_0-5],[SED_6-12],[SED_13-17],[SMI_18-21],[SMI_22-64],[SMI_65+])
) as Pvt
order by fiscal_year

Tom

Answer 2

Hi @Thomas Wikle

You cannot COUNT(distinct patient_id) in PIVOT clause，because patient_id is not given by your left_table_expression.
Your left_table_expression gives COUNT(distinct patient_id) ,so your need to give "COUNT(distinct patient_id)" an alia ,and then aggregate_func(<your_alia>) in PIVOT statement.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.