In the following example, there is more than one value column and the COLUMN_LIST takes the form: 'a, x, ...', 'b, y, ...', 'c, z, ...', where a, x, b, y, c, z, and so on are the names of the columns to be unpivoted into the value columns.
Each individual list, for example, 'a, x, ...', defines which columns are mapped to the value columns for one of the rows of output.
The input table T has columns id, year, jan_sales, jan_expense, feb_sales, feb_expense, ..., dec_sales, dec_expense.
SELECT * from UNPIVOT(
ON( select * from T)
USING
VALUE_COLUMNS('monthly_sales', 'monthly_expense')
UNPIVOT_COLUMN('month')
COLUMN_LIST('jan_sales, jan_expense', 'feb_sales, feb_expense', ..., 'dec_sales, dec_expense')
COLUMN_ALIAS_LIST('jan', 'feb', ..., 'dec' )
)X;
The output columns are the following: id, year, month, monthly_sales, monthly_expense.
One row of input, for example:
id year jan_sales jan_exp feb_sales feb_exp...dec_sales dec_exp 123 2012 100 50 200 100 ... 1200 600
generates 12 rows of output:
id year month monthly_sales monthly_expense
123 2012 jan 100 50
123 2012 feb 200 100
...
123 2012 dec 1200 600