Cannot get Pivot to work in SQL Server

Thomas Wikle 1 Reputation point
2022-01-27T17:59:42.453+00:00

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:
169108-image.png

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?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2022-01-27T20:37:09.483+00:00
    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

    0 comments No comments

  2. LiHong-MSFT 10,046 Reputation points
    2022-01-28T02:38:07.967+00:00

    Hi @Thomas Wikle
    169281-image.png
    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.