You need to
UNPIVOT[
^] data, than to use
aggregate functions[
^] ;)
See below example:
DECLARE @tmp TABLE (Total DECIMAL(8,2), [National] DECIMAL(8,2), [Foreign] DECIMAL(8,2), Category VARCHAR(30))
INSERT INTO @tmp (Total, [National], [Foreign], Category )
VALUES(1.00 , 2.00 , 98.00, 'Professional'),
(1.00 , 2.00 , 98.00, 'Professional'),
(1.00 , 4.00 , 96.00, 'Professional'),
(2.00 , 4.00 , 96.00, 'Marketing'),
(2.00 , 4.00 , 96.00, 'Marketing')
SELECT Category, Description, SUM(Value) AS SumOfValue
FROM (
SELECT Category, [Description], [Value]
FROM (
SELECT *
FROM @tmp
) AS pvt
UNPIVOT ([Value] FOR [Description] IN ([Total],[National], [Foreign])) AS unpvt
) AS T
GROUP BY Category, [Description]
ORDER BY Category, [Description]
Result:
Category Description SumOfValue
Marketing Foreign 192.00
Marketing National 8.00
Marketing Total 4.00
Professional Foreign 292.00
Professional National 8.00
Professional Total 3.00
If you don't want to display
Category
, remove it from first
SELECT
list and
GROUP BY
option.
Of course, it is possible to pivot data again:
SELECT [Description], [Professional], [Marketing]
FROM (
SELECT Category, [Description], [Value]
FROM (
SELECT *
FROM @tmp
) AS pvt
UNPIVOT ([Value] FOR [Description] IN ([Total],[National], [Foreign])) AS unpvt
) AS DT
PIVOT(SUM([Value]) FOR [Category] IN ([Professional], [Marketing])) AS PT
Than result is:
Category Professional Marketing
Foreign 292.00 192.00
National 8.00 8.00
Total 3.00 4.00