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
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
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
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)
WHERE UNPVT.VALUE != 0
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.