- Home /

Is it possible to calculate row totals in a pivot query (SQL 2005)?

Comment

Rob Farley

Best Answer

**Answer** by Jay Bonk
·
Jan 19, 2010 at 02:29 PM

This is the only way in which I'm aware of calculating the row in a pivot query

```
CREATE TABLE #T
(
KeyValue INT
,ItemId INT
,Amount INT
)
INSERT INTO #T
SELECT 1,1,1
UNION SELECT 1,2,1
UNION SELECT 1,1,3
UNION SELECT 1,2,4
UNION SELECT 1,3,1
UNION SELECT 1,3,3
UNION SELECT 1,3,4
UNION SELECT 2,2,9
UNION SELECT 2,1,3
UNION SELECT 2,2,4
UNION SELECT 2,3,1
UNION SELECT 2,3,3
UNION SELECT 2,3,7
SELECT KeyValue
,[1]
,[2]
,[3]
,[1] + [2] + [3] AS RowTotal
FROM
(
SELECT KeyValue
,ItemId
,Amount
FROM #T
) t
PIVOT
(
SUM(Amount) FOR ItemId IN ([1],[2],[3])
) AS p
GO
DROP TABLE #T
```

**Answer** by Rob Farley
·
Jan 19, 2010 at 07:37 PM

If you don't use the PIVOT operator, it becomes trivial. And let's face it, PIVOT is just a shortcut for doing it this way:

```
SELECT
SalesPersonID,
SUM(CASE WHEN Year(OrderDate) = 2002 THEN SalesAmount END) AS Sales2002,
SUM(CASE WHEN Year(OrderDate) = 2003 THEN SalesAmount END) AS Sales2003,
SUM(CASE WHEN Year(OrderDate) = 2004 THEN SalesAmount END) AS Sales2004,
SUM(SalesAmount) AS RowTotal
FROM Sales.OrderHeader
GROUP BY SalesPersonID;
```

But really, just do it in the client. If you put a Matrix in a report, then you should be able to put a row total in there easily.

Copyright 2018 Redgate Software. Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Ask SSC Site Issues (meta-askssc)
- Explore
- Topics
- Questions
- Users
- Badges