Search code examples
sqlsql-servert-sqlsql-server-2017

Query for calling months as column and sum of values in month as rows

I have a table something similar to below.

Regions | Num | Date
USA       1     20/9/2021
USA       2     20/9/2021
USA       1     20/9/2021
USA       4     20/9/2021
USA       1     05/8/2021
USA       1     05/8/2021
USA       6     05/8/2021

What I want to achieve is the below

Name of KPI            | AUG 21 | SEP 21
Total number for month     8       8

I am currently using pivot to do the same but each time we add a new month I have to update my query. Trying to make it a bit more automated.

Solution

  • Please try with dynamic pivot. It's very easy to implement. You can take help from below link.

    SQL Server dynamic PIVOT query?