dynamic grouping by month in SQL

Question

I am using CASE statements to group data into Month columns, like this:

SUM(CASE WHEN MONTH(date) =  1 THEN ROUND(value) END) as jan,
SUM(CASE WHEN MONTH(date) =  2 THEN ROUND(value) END) as feb,
SUM(CASE WHEN MONTH(date) =  3 THEN ROUND(value) END) as mar

Is it possible to NOT have to define the different CASE groupings?

I want to define the data range in the WHERE statement, and then have the report group by month, for whatever range I define. For example, maybe my report starts with July'20, and not Jan.

Is this possible in an SQL query?

Thanks

edit - example output:

+-------+-------+------+-------+-------+
|       | July  | Aug  |  Sep  |  etc  |
+-------+-------+------+-------+-------+
| value | 435 € | 24 € | 234 € | 453 € |
+-------+-------+------+-------+-------+

edit - possible solution/workaround:

if I do the following, it can be considered "semi-dynamic". I still need to define the month "buckets", but they can be trigged by the starting date (the month({ d '2021-01-01' }) part can also later be replaced with a variable, so that is also fixed in the code.

SUM (CASE WHEN MONTH(date) =  month({ d '2021-01-01' }) THEN value END) as month_1,
SUM (CASE WHEN MONTH(date) =  month({ d '2021-01-01' })+1 THEN value END) as month_2,
SUM (CASE WHEN MONTH(date) =  month({ d '2021-01-01' })+2 THEN value END) as month_3,
etc 

the main downside is that I have to hard-code the number of month groupings. So i'd be happy to hear of a better solution!

Answer 1

You can create a dynamic sql statement to generate the result into a temporary table and then return the result from the temporary table. Here is an example:

declare sqlStr string;
sqlStr = 'SELECT 1 AS column1, 2 AS column2 INTO #temp FROM system.iota';

execute immediate sqlStr;

select * from #temp;

The logic to generate the required grouping is up to you. You will need to use the while loop to construct the statement text.