How can I get values of the Rating column together with their definition for every specific month by using Pivot with Month in MSSQL

Question

Example :

Data:

GraphDetails
|----------------------------------------------------------|
| Id  GoalId  Definition                Rating  DateCrated |
| -------------------------------------------------------- |
| 1   2       Zero Infra Back Log       100     2020-02-05 |
| 2   2       Happy Customers           95      2020-02-05 |
| 3   2       Complete All Projects     100     2020-02-05 |
| 4   1       Zero Infra Back Log       100     2020-02-05 |
| 5   1       Happy Customers           98      2020-02-05 |
| 6   1       Complete All Projects     100     2020-02-05 |
|----------------------------------------------------------|

Query:

SELECT
 [1] AS Jan,
 [2] AS Feb,
 [3] AS Mar,
 [4] AS Apr,
 [5] AS May,
 [6] AS Jun,
 [7] AS Jul,
 [8] AS Aug,
 [9] AS Sep,
 [10] AS Oct,
 [11] AS Nov,
 [12] AS [Dec]
FROM
(Select Id, MONTH(DateRecorded) as TMonth
  FROM GraphDetails
    WHERE YEAR(DateRecorded) = 2020 AND GoalId = 1
    ) source
PIVOT
( Definition, Rating
    FOR TMonth
    IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) 
) AS pvtMonth

I get this error:

Incorrect syntax near ','.

In line with " Definition, Rating "

Result should look like this:

|-------------------------------------------------------------------------------------------------|
| Definition              | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
|-------------------------------------------------------------------------------------------------|
| Zero Infra Back Log     | 0   | 100 | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   |
| Happy Customers         | 0   | 98  | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   |
| Complete All Projects   | 0   | 100 | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   | 0   |
|-------------------------------------------------------------------------------------------------|

Is this possible? Any help, please!

Thank you!

*The code above was based on PIVOT with MONTH().

Answer 1

You could just use conditional aggregation:

select 
    definition,
    sum(case 
        when dateRecorded >= datefromparts(2020, 1, 1) and dateRecorded < datefromparts(2020, 2, 1)
        then rating
        else 0
    end) Jan,
    sum(case 
        when dateRecorded >= datefromparts(2020, 2, 1) and dateRecorded < datefromparts(2020, 3, 1)
        then rating
        else 0
    end) Feb,
    ...
    sum(case 
        when dateRecorded >= datefromparts(2020, 12, 1) and dateRecorded < datefromparts(2021, 1, 1)
        then rating
        else 0
    end) Dec
from graphDetails
where dateRecorded >= datefromparts(2020, 1, 1) and dateRecorded < datefromparts(2021, 1, 1)
group by definition
order by definition

This syntax is somehow more flexible than the specific PIVOT operator (and also, for what it's worth, it works across different database products).

Note that I modified the filters on dates so it uses half-open intervals, and no date function is applied on column dateRecorded: this should allow the database to take advantage of an index on the date column (and would also smoothly handle the time component of dateCreated, if any).

Answer 2

Query:

SELECT [Definition], isnull([1],0) AS Jan,
         isnull([2],0) AS Feb,
         isnull([3],0) AS Mar,
         isnull([4],0) AS Apr,
         isnull([5],0) AS May,
         isnull([6],0) AS Jun,
         isnull([7],0) AS Jul,
         isnull([8],0) AS Aug,
         isnull([9],0) AS Sep,
         isnull([10],0) AS Oct,
         isnull([11],0) AS Nov,
         isnull([12],0) AS [Dec]
        FROM
       (Select Id, MONTH(DateCrated) as TMonth,[Definition],Rating
          FROM GraphDetails
       WHERE YEAR(DateCrated) = 2020 AND GoalId = 1
        ) [source]
    PIVOT
    (  
    sum(Rating)
        FOR TMonth 
        IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12] ) 
    ) AS pvtMonth

Result :