asked
How to increase column name or value in SQL
I have below columns in my table EtatMonthRolling select [ID] [Month_201801], [Month_201802], [Month_201803], [Month_201804], [Month_201805], [Month_201806], [Month_201807], [Month_201808], [Month_201809], [Month_201810], [Month_201811], [Month_201812], [Month_201901], [Month_201902], [Month_201903], [Month_201904], [Month_201905], [Month_201906], [Month_201907], [Month_201908], [Month_201909], [Month_201910], [Month_201911], [Month_201912] from MonthRolling And i want to select 12 month rolling for forecast eg-> if we are in 2018/02 then i want to select 2018/03 .......till 2019/02 if we are in 2018/03 then i want to select 2018/04....... till 2019/03 I achieved this by unpivot the column (to convert columns to rows) then performed the monthRolling function then again pivot the column. But as my column name is dynamic i am facing issue to store this into table. So i want to store column as [Forecast Month + 1], [Forecast Month + 2], [Forecast Month + 3] ....... [Forecast Month + 12] Note- I tried using dynamic sql, but i personally feel is not good option. So could you please suggest how can i achieve this. Create Table: CREATE TABLE [dbo].[EtatMonthRolling]( [ID] [varchar](100) NULL, [Month_201701] [int] NULL, [Month_201702] [int] NULL, [Month_201703] [int] NULL, [Month_201704] [int] NULL, [Month_201705] [int] NULL, [Month_201706] [int] NULL, [Month_201707] [int] NULL, [Month_201708] [int] NULL, [Month_201709] [int] NULL, [Month_201710] [int] NULL, [Month_201711] [int] NULL, [Month_201712] [int] NULL, [Month_201801] [int] NULL, [Month_201802] [int] NULL, [Month_201803] [int] NULL, [Month_201804] [int] NULL, [Month_201805] [int] NULL, [Month_201806] [int] NULL, [Month_201807] [int] NULL, [Month_201808] [int] NULL, [Month_201809] [int] NULL, [Month_201810] [int] NULL, [Month_201811] [int] NULL, [Month_201812] [int] NULL, [Month_201901] [int] NULL, [Month_201902] [int] NULL, [Month_201903] [int] NULL, [Month_201904] [int] NULL, [Month_201905] [int] NULL, [Month_201906] [int] NULL, [Month_201907] [int] NULL, [Month_201908] [int] NULL, [Month_201909] [int] NULL, [Month_201910] [int] NULL, [Month_201911] [int] NULL, [Month_201912] [int] NULL ) Code: SELECT [ID],[MonthName] /*, [MonthForecast] AS [Inventory]*/ FROM ( SELECT *, CONVERT(DATE,(LEFT(RIGHT([MonthName],6),4) + '-' + RIGHT(RIGHT([MonthName],6),2) + '-' + '01' )) AS MonthDate -- Converting MonthName to Date FROM ( SELECT [ID], [MonthName], [MonthForecast] FROM ( SELECT * FROM ( SELECT [ID], [month_201701], [month_201702], [month_201703], [month_201704], [month_201705], [month_201706], [month_201707], [month_201708], [month_201709], [month_201710], [month_201711], [month_201712], [month_201801], [month_201802], [month_201803], [month_201804], [month_201805], [month_201806], [month_201807], [month_201808], [month_201809], [month_201810], [month_201811], [month_201812], [month_201901], [month_201902], [month_201903], [month_201904], [month_201905], [month_201906], [month_201907], [month_201908], [month_201909], [month_201910], [month_201911], [month_201912], /*, DENSE_RANK() OVER (ORDER BY CONVERT(DATE,SyncDate) desc) AS LatestRecord */ FROM EtatMonthRolling )A /*WHERE LatestRecord=1*/)B UNPIVOT ( [MonthForecast] FOR [MonthName] IN ( [month_201701], [month_201702], [month_201703], [month_201704], [month_201705], [month_201706], [month_201707], [month_201708], [month_201709], [month_201710], [month_201711], [month_201712], [month_201801], [month_201802], [month_201803], [month_201804], [month_201805], [month_201806], [month_201807], [month_201808], [month_201809], [month_201810], [month_201811], [month_201812], [month_201901], [month_201902], [month_201903], [month_201904], [month_201905], [month_201906], [month_201907], [month_201908], [month_201909], [month_201910], [month_201911], [month_201912] )) AS UnPvot)A)B WHERE MonthDate < DATEADD(MONTH,12,GETDATE()) and MonthDate > DATEADD(MONTH,0,GETDATE())
@GayatriPanigrahi The problem lies in the design of the table. Instead of storing month values in a single column, data is some how being stored in multiple columns? What kind of data is stored in these columns? Please post some dummy data and the required output, may be it could clear the requirement a bit more.
@GayatriPanigrahi Why do you need such a strange design (with months as columns)? Also, what are you going to do with the columns which are toward the end of the table? For example, column named **month\_201911** has only one column into the future month relative to it, so there is no 12 months rolling forecast for this column, the whole forecast is nothing but the value in the **month\_201912** column . The next column, namely **month\_201912** does not have any forecast values so it's own forecast value is guaranteed to be NULL. What I am trying to say is that it is actually possible to design the table properly and then come up with the query to display pivoted results.
@GayatriPanigrahi Too bad that my answers to your questions do not appear to help even though I believe that they provided solutions precisely as requested. I am referring to [Check value exist in previous month in sql][1] and [Calculate value using previous and current month][2]. In fact, you don't appear to comment and/or upvote and/or accept any of the answers. [ID repetition with respect to month in SQL][3] answered by @Kev Riley ♦♦ shows the same lack of any response (a very good answer by the way). In a mean time, responses might help the new site users who have similar questions to find answers which someone else already marked as helpful so they don't have to repeat history. [1]:
https://ask.sqlservercentral.com/questions/145936/check-value-exist-in-previous-month-in-sql.html [2]:
https://ask.sqlservercentral.com/questions/145907/calculate-value-using-previous-and-current-month.html [3]:
https://ask.sqlservercentral.com/questions/145844/id-repetition-with-respect-to-month-in-sql.html
Hi Oleg, Appreciate your proactive response for every query. I know this is very horrible database structure, but this is totally on client demand. And every year it require hard code changes where new 12Month will be added (eg-> till i have 201912 data then next year client will send data 202001......202012) and it keeps on increasing every year. I know this is very difficult to maintain as well, but have to do. But i got stuck in dynamic name (earlier explanation) change.
19 People are following this question.
