Need to manipulate field values using UNPIVOT by referencing the column names being unpivoted

Question

I'm new to stackoverflow and new to UNPIVOT so I apologize if the answer is out there somewhere, but I can't find it - perhaps I don't know how to word it properly. Also please forgive me lack of knowledge on h

I have values in a table BUDGET_INFO containing financial information for each month in a single row.

BUD_PROJ    BUD_ACCT   BUD_SUB_ACCT    BUD_YEAR    BUD_MONTH_01    BUD_MONTH_02    BUD_MONTH_03   
1           10000      01              2015        1000            2000            3000

I was able to get it as such

BUD_PROJ    BUD_ACCT   BUD_SUB_ACCT    BUD_YEAR   AMOUNT
1           1000       01              2015       1000
1           1000       01              2015       2000
1           1000       01              2015       3000

The problem is, I need to know what month the values come from. End result needs to be:

BUD_PROJ    BUD_ACCT   BUD_SUB_ACCT    BUD_YEAR    VALUE
1           1000       01              201501      1000
1           1000       01              201502      2000
1           1000       01              201503      3000

Not all monthly column values will be populated, so I'd like to be able to reference the column name and pull the month identifier ('01','02','03',etc) and concatenate it with the year value (ideally) or just into its own column. Is there any way to reference the column header from the values in the select statement? So far I used:

SELECT
  UNPVT.BUD_PROJ,
  UNPVT.BUD_YEAR,
  UNPVT.BUD_ACCT,
  UNPVT.BUD_SUB_ACCT,
  UNPVT.VALUE
FROM BUDGET_INFO
UNPIVOT 
(
VALUE FOR ATTRIBUTE IN 
(BUD_MONTH_01, BUD_MONTH_02, BUD_MONTH_03,BUD_MONTH_04, BUD_MONTH_05, BUD_MONTH_06, BUD_MONTH_08, BUD_MONTH_09, BUD_MONTH_10, BUD_MONTH_11, BUD_MONTH_12)
) UNPVT
WHERE UNPVT.VALUE != 0

Thanks all.

Answer 1

If you are using SQL Server, then apply can readily do what you want:

select bi.BUD_PROJ, bi.BUD_YEAR, bi.BUD_ACCT, bi.BUD_SUB_ACCT,
       v.value. v.mon
from budget_info bi cross apply
     (values (BUD_MONTH_01, 1),
             (BUD_MONTH_02, 2),
             . . . 
     ) v(value, mon))
where v.value <> 0;

apply actually implements a lateral join, which is available in other databases as well.

If you don't use apply, you can do something quite similar with a bunch of union alls, with each subquery selecting one column.