Using different aggregate functions in a pivot

Posted on

Question :

I have a table of data collected in the time as shown below.

I would pivot them using different aggregate functions: average for C1 and sum for the others.

table

How could I achieve it ?

DECLARE @T AS table
(
    TS  character(6) NOT NULL,
    C1  integer NOT NULL,
    C2  integer NOT NULL,
    C3  integer NOT NULL,
    C4  integer NOT NULL
);

INSERT @T
    (TS, C1, C2, C3, C4)
VALUES
    ('201501', 4, 6, 3, 2),
    ('201502', 6, 2, 3, 2),
    ('201503', 6, 1, 3, 2),
    ('201504', 4, 0, 3, 2);

Answer :

Try this:

SELECT
    label
    , AVG(value) AS [2015]
FROM @T
UNPIVOT (
    value
    FOR label IN (C1)
) upvt
GROUP BY label
UNION
SELECT

    label
    , SUM(value) AS [2015]
FROM @T
UNPIVOT (
    value
    FOR label IN (C2,C3,C4)
) upvt
GROUP BY label

Leave a Reply

Your email address will not be published. Required fields are marked *