Mutiple aggregates in PIVOT - by Mark Yudkin

Status : 


Sign in
to vote
ID 236237 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 11/7/2006 2:16:23 AM
Access Restriction Public
Primary Feedback Item 127071


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). 

Sign in to post a comment.
Posted by 7846 on 8/26/2016 at 6:38 AM
Yes add support for multiple aggregates, please.

Also please add support for multiple columns in the FOR clause.

Oracle has it since 11g:

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));


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
Posted by sjcMSUser on 2/9/2016 at 5:21 AM
Yes, please. This would be very useful.
Posted by Mark Yudkin on 3/10/2011 at 11:49 PM
I do not agree that this request is a duplicate of feedback 127071. That feedback is for a dynamic column list (dynamic IN) , where the internal query determines the shape of the output result set. This query is to support multiple aggregates; the shape of the output result set remains static. Specifically I am not suggesting any change in the IN (<column_list>) clause, which is the target of 127071.

A concrete examples that we have face is when dealing with currencies - we need to simultanously aggregate the local currency and the foreign currency amounts. Currently we do this using the brute force approach (with a long list of sum (case ...)
Posted by Microsoft on 3/10/2011 at 6:15 PM
Hello Mark,

I have resolved your request as duplicate of one below:

We will track your feature request as part of that one.

Umachandar, SQL Programmability Team
Posted by Microsoft on 11/13/2006 at 6:46 PM
Thank you for submitting this feature request. This is now under consideration for upcoming version of SQL Server.