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