Crosstab Using Alias Variables

Question

I have the following dataset which looks like this:

 ID     Date             PT Type
 1      1/01/2018       Excellent
 2      1/11/2018       Good
 3      2/1/2018        Excellent
 4      4/18/2018       Fair
 5      7/23/2018       Bad
 6      1/12/2019       Excellent
 6      3/18/2019       Excellent
 6      8/2/2019        Good

I want to create a crosstab table that aggregates the data based on year and month which looks like this (or close to it)

 Year    Month    Excellent    Good    Fair    Bad
 2018    January     2          5       4       3
 2018    February    7          0       2       9
 ....

I used the following stackoverflow link to get the framework of the code I need but am running into issues because I need to create two alias variable based on the Date variable.

I need to know how to create a crosstab query

The code I have now looks like this but I'm not sure how to complete it.

SELECT PT_TYPE , 
  sum(case when PT_TYPE = 'Excellent' then 1 else 0 end) [Excellent], 
  sum(case when PT_TYPE = 'Good' then 1 else 0 end) [Good], 
  sum(case when PT_TYPE = 'Fair' then 1 else 0 end) [Fair]
from
(
  select count(*) over(partition by Date)
  from ED_TAT_MASTER
)

You don't need to select the `PT_TYPE` since you are aggregating off this column for your new columns. You'll instead want to use `datepart` to get your `year` and `month`. Also, the `FROM` shouldn't be a sub query... just list your table here and you're set after you apply your group by! — S3S, Mar 11 '19 at 19:08

score 4 · Accepted Answer · answered Mar 11 '19 at 19:08

subquery is not needed, just year() & month() in group by clause & do aggregation :

SELECT YEAR(DATE), MONTH(DATE),
       SUM(case when PT_TYPE = 'Excellent' then 1 else 0 end) [Excellent], 
       SUM(case when PT_TYPE = 'Good' then 1 else 0 end) [Good], 
       SUM(case when PT_TYPE = 'Fair' then 1 else 0 end) [Fair]
FROM ED_TAT_MASTER
GROUP BY YEAR(DATE), MONTH(DATE);

Crosstab Using Alias Variables

1 Answers1