In the following example, 12 columns (shown in COLUMN_LIST) are unpivoted to 1 column with 12 rows.
The input table T in the query_expression (in the following SELECT statement) has the following columns: id, year, jan_sales, feb_sales, ..., dec_sales
SELECT * from TD_UNPIVOT(
ON( select * from T)
USING
VALUE_COLUMNS('monthly_sales')
UNPIVOT_COLUMN('month')
COLUMN_LIST('jan_sales', 'feb_sales', ..., 'dec_sales')
COLUMN_ALIAS_LIST('jan', 'feb', ..., 'dec' )
)X;
The output columns are the following: id, year, month, monthly_sales.
One row of input, for example:
id
year
jan_sales
feb_sales
... dec_sales
123 2012 100 200 ... 1200
generates 12 rows of output:
id
year
month
monthly_sales
123 2012 jan 100
123 2012 feb 200
...
123 2012 dec 1200
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