Pivot monthly data from the past years

Here’s the data from my original query:

Store Name	Month	Transaction Count
0001 – Loc1	2018December	245
0002 – Loc2	2018December	261
0001 – Loc1	2018November	232
0002 – Loc2	2018November	218
0001 – Loc1	2018October	123
0002 – Loc2	2018October	144
0001 – Loc1	2019January	312
0002 – Loc2	2019January	287

I’m trying to convert Transaction Count and Month (w/ year) columns into rows. I’m hoping for this result:

Store Name	2018October	2018November	2018December	2019January
0001 – Loc1	123	232	245	312
0002 – Loc2	144	218	261	287

The query I’m currently trying resulted to this message : Msg 156, Level 15, State 1, Line 23 Incorrect syntax near the keyword ‘DISTINCT’.

Here’s the latest query with pivot:

(SELECT b.[Name] 
        ,CONCAT(YEAR(a.[Date]),'',DATENAME(MONTH,a.[Date])) AS 'Month'
        ,COUNT(DISTINCT a.[Receipt Num]) AS 'Count'
        ,a.[Store Num]
FROM [dbo].[TransSales] a

LEFT JOIN [dbo].[Location] b
      ON b.[Code]=a.[Store Num]

WHERE a.[Store Num] in ('0001','0002') AND
      a.[Date] BETWEEN '2018-10-01' AND '2021-06-30' 

GROUP BY b.[Name]
         ,CONCAT(YEAR(a.[Date]),'',DATENAME(MONTH,a.[Date]))
         ,a.[Store Num]
 )


SELECT * 
FROM SALES
PIVOT
(COUNT (DISTINCT [Receipt Num])
    FOR [Month] IN ([2018December]
,[2018November]
,[2018October]
,[2019January]
)) P


;

Answer

Per my comment, I think you should be handling this pivot in your presentation layer, be it Excel, SSRS, Power BI or whatever. Going forwards, this will be a much more maintainable solution as you will not need to either keep updating your Month column listing or rely on dynamic SQL.

Also, I would advise that you stop using reserved keywords such as Date, Month and Count as a column name and that you properly alias your tables rather than using a, b, c, etc. These things make the code harder to read, write and maintain for literally zero benefit.

That said, if you plan on persisting down this route, your issue in the code is related to not referencing your source columns correctly. Assuming that your initial query is within a CTE, you should be referring to the Count column name rather than count(distinct [Receipt Num]) that populates it.

Consequently, your script should look something like this:

with sales as
(
    select l.[Name] as LocationName
            ,concat(year(s.[Date]),'',datename(month,s.[Date])) as TransactionMonth
            ,count(distinct s.[Receipt Num]) as Transactions
            ,s.[Store Num]
    from [dbo].[TransSales] as s
        left join [dbo].[Location] as l
              on s.[Store Num] = l.[Code]
    where s.[Store Num] in ('0001','0002')
      and s.[Date] between '20181001' and '20210630' 
    group by l.[Name]
            ,concat(year(s.[Date]),'',datename(month,s.[Date]))
            ,s.[Store Num]
)
select * 
from sales
pivot(sum(Transactions)
      for TransactionMonth in ([2018December]
                              ,[2018November]
                              ,[2018October]
                              ,[2019January]
                              )
      ) as p
;