SQL Query - Dynamic Month Change based on Current Date

Question

I have this table in SQL. [Tbl_SALES]

+-------------+-----------+-------------+---------+-------+------+-------+
| Cust_Number |Code_Number| PartNumber  | UserID  | Month | Year | Sales |
+-------------+-----------+-------------+---------+-------+------+-------+
|     5535536 |       101 | AB11111     | Luis    | Aug   |   16 |   100 |
|     9256819 |       101 | AV11111     | Luis    | Aug   |   16 |   100 |
|     4369983 |       101 | AX11111     | Luis    | Aug   |   16 |   100 |
|     5535536 |       101 | AB11111     | Luis    | Sep   |   16 |   200 |
|     9256819 |       101 | AV11111     | Luis    | Sep   |   16 |   200 |
|     4369983 |       101 | AX11111     | Luis    | Sep   |   16 |   200 |
|     5535536 |       101 | AB11111     | Luis    | Oct   |   16 |   100 |
|     4369983 |       101 | AX11111     | Luis    | Oct   |   16 |   200 |
|     6441842 |       101 | AA11111     | Luis    | Oct   |   16 |   200 |
|     5535536 |       101 | AB11111     | Luis    | Nov   |   16 |   100 |
|     5535536 |       101 | AB11111     | Luis    | Dec   |   16 |   100 |
|     6441842 |       101 | AA11111     | Luis    | Dec   |   16 |   100 |
|     5535536 |       101 | AB11111     | Luis    | Jan   |   17 |   100 |
|     6441842 |       101 | AA11111     | Luis    | Jan   |   17 |   100 |
|     4369983 |       101 | AX11111     | Luis    | Feb   |   17 |   300 |
|     6441842 |       101 | AA11111     | Luis    | Feb   |   17 |   100 |
+-------------+-----------+-------------+---------+-------+------+-------+

I need to transpose the Month rows into Columns. I have this SQL Query that transpose all months correctly.

SELECT
[Cust_Number],
[Code_Number], 
[PartNumber], 
[UserID], 
SUM([Aug'16]) as [Aug'16],
SUM([Sep'16]) as [Sep'16],
SUM([Oct'16]) as [Oct'16],
SUM([Nov'16]) as [Nov'16],
SUM([Dec'16]) as [Dec'16],
SUM([Jan'17]) as [Jan'17],
SUM([Feb'17]) as [Feb'17],
SUM([Mar'17]) as [Mar'17],
SUM([Apr'17]) as [Apr'17],
SUM([May'17]) as [May'17],
SUM([Jun'17]) as [Jun'17],
SUM([Jul'17]) as [Jul'17]
FROM (
SELECT
[Cust_Number],
[Code_Number], 
[PartNumber], 
[UserID], 
case when  ([MONTH] = 'Aug') then SUM([SALES]) else Null end as [Aug'16],
case when  ([MONTH] = 'Sep') then SUM([SALES]) else Null end as [Sep'16],
case when  ([MONTH] = 'Oct') then SUM([SALES]) else Null end as [Oct'16],
case when  ([MONTH] = 'Nov') then SUM([SALES]) else Null end as [Nov'16],
case when  ([MONTH] = 'Dec') then SUM([SALES]) else Null end as [Dec'16],
case when  ([MONTH] = 'Jan') then SUM([SALES]) else Null end as [Jan'17],
case when  ([MONTH] = 'Feb') then SUM([SALES]) else Null end as [Feb'17],
case when  ([MONTH] = 'Mar') then SUM([SALES]) else Null end as [Mar'17],
case when  ([MONTH] = 'Apr') then SUM([SALES]) else Null end as [Apr'17],
case when  ([MONTH] = 'May') then SUM([SALES]) else Null end as [May'17],
case when  ([MONTH] = 'Jun') then SUM([SALES]) else Null end as [Jun'17],
case when  ([MONTH] = 'Jul') then SUM([SALES]) else Null end as [Jul'17]

FROM [Test].[dbo].[Tbl_SALES]
where [UserID] = 'Luis'
group by 
[Cust_Number],
[Code_Number], 
[PartNumber], 
[UserID], 
[MONTH]
) a
group by 
[Cust_Number],
[Code_Number], 
[PartNumber], 
[UserID]

Query Result.

+---------------+-------------+-------------+------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+----------+
| Cust_Name     | Code_Number | PartNumber  | User | Aug'16 | Sep'16 | Oct'16 | Nov'16 | Dec'16 | Jan'17 | Feb'17 | Mar'17 | Apr'17 | May'17 | Jun'17 | Jul'17 | |
+---------------+-------------+-------------+------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+----------+
|       5535536 |         101 | AB11111     | Luis |    100 |    200 |    100 |    100 |    100 |    100 |        |        |        |        |        |          |
|       9256819 |         101 | AV11111     | Luis |    100 |    200 |        |        |        |        |        |        |        |        |        |          |
|       4369983 |         101 | AX11111     | Luis |    100 |    200 |    200 |        |        |        |    300 |        |        |        |        |          |
|       6441842 |         101 | AA11111     | Luis |        |        |    200 |        |    100 |    100 |    100 |        |        |        |        |          |
+---------------+-------------+-------------+------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+----------+

Considering the previous query ... How can I make this query dynamic based on the current date? Let's say, today is 08/29/2016, then the first month in the case list should be Aug'16, however, do you have any idea on how to improve this and make this automatically instead of changing it every month by me?

If you want the columns to be named dynamically, [you'll need to use dynamic SQL](https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/). Or, you could return normalized, grouped data to the consumer, and *they* can pivot the data. — Aaron Bertrand, Aug 29 '16 at 19:23

score 0 · Answer 1 · edited May 23 '17 at 11:51

0

What you need is Pivot Tables

They support dynamic columns

More examples: https://blogs.msdn.microsoft.com/spike/2009/03/03/pivot-tables-in-sql-server-a-simple-sample/

Also look at this SO post. It shows an example of dynamically generating week numbers, but you could easily adopt it to months: Convert Rows to columns using 'Pivot' in SQL Server

edited May 23 '17 at 11:51

Community

1
1

answered Aug 30 '16 at 16:52

Dimitri

6,923
4
35
49

SQL Query - Dynamic Month Change based on Current Date

1 Answers1