Simple demo:
DECLARE @demo
TABLE (
group_id INTEGER NOT NULL,
value MONEY NOT NULL
);
INSERT @demo (group_id, value) VALUES (1, $1.00);
INSERT @demo (group_id, value) VALUES (1, $2.00);
INSERT @demo (group_id, value) VALUES (1, $3.00);
INSERT @demo (group_id, value) VALUES (1, $4.00);
INSERT @demo (group_id, value) VALUES (2, $5.00);
INSERT @demo (group_id, value) VALUES (2, $4.00);
INSERT @demo (group_id, value) VALUES (3, $3.00);
INSERT @demo (group_id, value) VALUES (3, $2.00);
INSERT @demo (group_id, value) VALUES (3, $7.00);
INSERT @demo (group_id, value) VALUES (4, $2.00);
SELECT group_id,
value
FROM @demo;
SELECT P.[1], P.[2], P.[3], P.[4]
FROM @demo D
PIVOT (
SUM(D.value) FOR
D.group_id IN ([1], [2], [3], [4])
) P;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi