SQL Coalesce or other Option?

Transact-SQL
1

I'm working with a sql table that has columns labeled as BOP201501, BOP201502, BOP201601, BOP201602, AOP201501, AOP201502, AOP201601, AOP201602, etc. Each Year a new Set of BOP and AOP columns gets added to the table with the Year and then an 01 - 12 value for 12 Calendar Periods. I do NOT have the option to redesign the table or how the columns get created. Poor design I know but it is the hand that I'm dealt. What I'm trying to do is create a SQL view that will pull the data from this table as LEFT(COLUMN,3) AS BudgetType, Substring(COLUMN,4,4) AS YEAR1
, Substring (COLUMN,8,2) as PeriodId, COLUMN as PeriodAmount

Basically I want a dynamic SQL View (if possible) to pivot the data in the columns and column headers/labels so that I can actually work with the data. I don't think Coalesce works and I can't figure out how to do it with a Pivot since the Column Names will continue to change. Any ideas on how to resolve this, again I didn't create the table and cannot change the method in which the table is appended to or used.

2

if you can create a dummy dataset and your expected result ?

3

I don't think you are going to be able to do this in a VIEW (i.e. without Dynamic SQL)

The column names, for the table, will be available in the sys.columns system table, so you could interrogate them there.

SELECT C.name
FROM sys.tables AS T
     JOIN sys.columns AS C
          ON C.object_id = T.object_id
WHERE T.name = 'YourTableName'

but I'm thinking the only answer is to regenerate the VIEWs each year ... :frowning: