


Currently the PIVOT operator is: PIVOT ( aggregate_function ( value_column ) FOR pivot_column IN ( <column_list> ) [ AS ] table_alias I would like to see this enhanced to: PIVOT ( aggregate_function ( value_column ) [ ,...n ] FOR pivot_column IN ( <column_list> ) [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] The resulting columns are ordered such that all of the aggregation function applications for a single pivot column value are consecutive. This would permit both multiple aggregation functions on a single value column (e.g. both MIN and MAX) or application of aggregation functions on multiple value columns (e.g. sales value and commission).
Also please add support for multiple columns in the FOR clause.
Oracle has it since 11g:
SELECT *
FROM pivot_data
PIVOT (
SUM(sal) AS sum, COUNT(sal) AS cnt
FOR deptno IN (
10 AS d10_sal,
20 AS d20_sal,
30 AS d30_sal,
40 AS d40_sal));
and
SELECT *
FROM pivot_data
PIVOT (
SUM(sal) AS sum, COUNT(sal) AS cnt
FOR (deptno,job) IN (
(30, 'SALESMAN') AS d30_sls,
(30, 'MANAGER') AS d30_mgr,
(30, 'CLERK') AS d30_clk));
I agree with Mark Yudkin, this request is in no way duplicate of Feedback 127071