Gradisek Gradisek - 2 months ago 13
SQL Question

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

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

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.

Comments