Using pivot table with column and row totals in sql server 2008

Question

I have a table with following columns

defect_id, developer_name, status, summary, root_cause, 
Secondary_RC, description, Comments, environment_name

The column root_cause has Enviro, Requi, Dev, TSc, TD, Unkn as its values and column environment_name has QA1, QA2, QA3

I need to prepare a report in the below format

    Enviro Requi  Dev TSc  TD Unkn  Total
QA1    9    1     14   17   2   3   46
QA2    8    1     14   0    5   1   29
QA3    1    1      7   0    0   1   10
Total 18    3     35   17   7   5   85

I have prepare the report till

    Enviro Requi  Dev  TSc  TD Unkn 
QA1    9    1     14    17   2  3   
QA2    8    1     14    0    5  1   
QA3    1    1      7    0    0  1

I used the below query to get the above result

select *
from
(
  select environment_name as " ", value
  from test1 
  unpivot
  (
     value
    for col in (root_cause)
  ) unp
) src
pivot
(
  count(value)
  for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
) piv

Can anyone help to get the totals for columns and rows?

Welcome to StackOverflow: if you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Jun 17 '13 at 04:49
What is the purpose of the `unpivot` part in this query? Surely, you could replace the entire subquery with just `select environment_name as " ", root_cause as value from test1`, no? — Andriy M, Jun 17 '13 at 06:31
possible duplicate of [Can you subtotal rows and/or columns in a pivot table?](http://stackoverflow.com/questions/7925748/can-you-subtotal-rows-and-or-columns-in-a-pivot-table) — bummi, Dec 01 '14 at 09:31

Andriy M · Accepted Answer · 2017-06-17T19:09:33.197

There may be various approaches to this. You can calculate all the totals after the pivot, or you can get the totals first, then pivot all the results. It is also possible to have kind of middle ground: get one kind of the totals (e.g. the row-wise ones), pivot, then get the other kind, although that might be overdoing it.

The first of the mentioned approaches, getting all the totals after the pivot, could be done in a very straightforward way, and the only thing potentially new to you in the below implementation might be GROUP BY ROLLUP():

SELECT
  [ ]      = ISNULL(environment_name, 'Total'),
  [Enviro] = SUM([Enviro]),
  [Requi]  = SUM([Requi]),
  [Dev]    = SUM([Dev]),
  [Tsc]    = SUM([Tsc]),
  [TD]     = SUM([TD]),
  [Unkn]   = SUM([Unkn]),
  Total    = SUM([Enviro] + [Requi] + [Dev] + [Tsc] + [TD] + [Unkn])
FROM (
  SELECT environment_name, root_cause
  FROM test1
) s
PIVOT (
  COUNT(root_cause)
  FOR root_cause IN ([Enviro], [Requi], [Dev], [Tsc], [TD], [Unkn])
) p
GROUP BY
  ROLLUP(environment_name)
;

Basically, the GROUP BY ROLLUP() part produces the Total row for you. The grouping is first done by environment_name, then the grand total row is added.

To do just the opposite, i.e. get the totals prior to pivoting, you could employ GROUP BY CUBE() like this:

SELECT
  [ ]      = environment_name,
  [Enviro] = ISNULL([Enviro], 0),
  [Requi]  = ISNULL([Requi] , 0),
  [Dev]    = ISNULL([Dev]   , 0),
  [Tsc]    = ISNULL([Tsc]   , 0),
  [TD]     = ISNULL([TD]    , 0),
  [Unkn]   = ISNULL([Unkn]  , 0),
  Total    = ISNULL(Total   , 0)
FROM (
  SELECT
    environment_name = ISNULL(environment_name, 'Total'),
    root_cause       = ISNULL(root_cause,       'Total'),
    cnt              = COUNT(*)
  FROM test1
  WHERE root_cause IS NOT NULL
  GROUP BY
    CUBE(environment_name, root_cause)
) s
PIVOT (
  SUM(cnt)
  FOR root_cause IN ([Enviro], [Requi], [Dev], [Tsc], [TD], [Unkn], Total)
) p
;

Both methods can be tested and played with at SQL Fiddle:

Note. I've omitted the unpivoting step in both suggestions because unpivoting a single column seemed clearly redundant. If there's more to it, though, adjusting either of the queries should be easy.

Little addition for Method 1 and MSSQL 2005 (yes, it is still used somewhere..) Sql2005 says that there is no such ROLLUP function, the solution is to replace "GROUP BY ROLLUP(environment_name)" with "GROUP BY environment_name WITH ROLLUP" which works for Sql2008 as well. Also not very clear why there is subquery in FROM - instead of "FROM (..) s" there can be just "FROM test1". In any case +1, very helpful! — sarh, Sep 26 '14 at 11:55
@sarh: Re: why subquery – if it was simply `FROM test1 PIVOT ...`, then PIVOT would use all the columns in `test1` (apart from the one specified as the aggregate function's argument) as grouping criteria, but we only need to group by `environment_name`. — Andriy M, Sep 26 '14 at 13:34
@AndriyM: not sure I understand.. I've added a column to your sample solution, populated it with some numbers, run without subquery and got the same result: http://sqlfiddle.com/#!3/8fe8c/1 — sarh, Sep 26 '14 at 13:51
@sarh: Indeed you are right. The difference would be only in the intermediate PIVOT result set: without a subquery there [could potentially be multiple entries with same `environment_name`](http://sqlfiddle.com/#!3/0ce81/3). But `GROUP BY ROLLUP(environment_name)` ultimately suppresses them and aggregates the counts, producing the same output as with the subquery. — Andriy M, Sep 26 '14 at 14:55

Sarath Subramanian · Answer 2 · 2015-01-04T09:56:40.453

You can find Total for root_cause and environment_name using ROLLUP.

RNO_COLTOTAL - Logic to place Total in last column, since the columns Tsc,Unkn will overlap the column Total when pivoting, since its ordering alphabetically.
RNO_ROWTOTAL - Logic to place Total in last row since a value that is starting with U,W,X,Y,Z can overlap the value Total, since its ordering alphabetically.
SUM(VALUE) - Can define on what aggregate function we can use with ROLLUP.

QUERY 1

SELECT CASE WHEN root_cause IS NULL THEN 1 ELSE 0 END RNO_COLTOTAL, 
CASE WHEN environment_name IS NULL THEN 1 ELSE 0 END RNO_ROWTOTAL,
ISNULL(environment_name,'Total')environment_name,
ISNULL(root_cause,'Total')root_cause,
SUM(VALUE) VALUE
INTO #NEWTABLE
FROM
(
    -- Find the count for environment_name,root_cause
    SELECT DISTINCT *,COUNT(*) OVER(PARTITION BY environment_name,root_cause)VALUE 
    FROM #TEMP
)TAB
GROUP BY root_cause,environment_name
WITH CUBE

We will get the following logic when CUBE is used

enter image description here

We declare variables for pivoting.

@cols - Column values for pivoting.
@NulltoZeroCols - Replace null values with zero.

QUERY 2

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + root_cause + ']', 
               '[' + root_cause + ']')
               FROM    (SELECT DISTINCT RNO_COLTOTAL,root_cause FROM #NEWTABLE) PV 
               ORDER BY  RNO_COLTOTAL,root_cause 

DECLARE @NulltoZeroCols NVARCHAR (MAX)

SET @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+root_cause+'],0) AS ['+root_cause+']' 
FROM(SELECT DISTINCT RNO_COLTOTAL,root_cause FROM #NEWTABLE GROUP BY RNO_COLTOTAL,root_cause)TAB  
ORDER BY RNO_COLTOTAL  FOR XML PATH('')),2,8000)

Now pivot it dynamically

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT environment_name,'+ @NulltoZeroCols +' FROM 
             (
                 SELECT RNO_ROWTOTAL,environment_name,root_cause,VALUE
                 FROM #NEWTABLE
             ) x
             PIVOT 
             (
                 MIN(VALUE)
                 FOR [root_cause] IN (' + @cols + ')
            ) p
            ORDER BY RNO_ROWTOTAL,environment_name;' 

EXEC SP_EXECUTESQL @query

RESULT

enter image description here

cha · Answer 3 · 2013-06-17T22:12:14.477

1

I think you need to calculate the Total separately. Using this simple query for the total (sorry, had to give the alias name for your " " column):

select environment_name as en, 
count (*) AS Total
FROM test1 
WHERE value in ('Enviro', 'Requi', 'Dev', 'Tsc', 'TD', 'Unkn')
GROUP BY environment_name

you can easily join both queries together to get the required report:

SELECT * FROM
(select *
from
(
  select environment_name as en, value
  from test1 
  unpivot
  (
     value
    for col in (root_cause)
  ) unp
) src
pivot
(
  count(value)
  for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
) piv
) AS a 
INNER JOIN
( select environment_name as en, 
  count (*) AS Total
  FROM test1 
  WHERE value in ('Enviro', 'Requi', 'Dev', 'Tsc', 'TD', 'Unkn')
  GROUP BY environment_name
 ) AS b ON a.en = b.en
UNION ALL
SELECT * FROM
(select *
from
(
  select 'Total' as en, value
  from test1 
  unpivot
  (
     value
    for col in (root_cause)
  ) unp
) src
pivot
(
  count(value)
  for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
) piv
) AS a 
INNER JOIN
( select 'Total' as en, 
  count (*) AS Total
  FROM test1 
  WHERE value in ('Enviro', 'Requi', 'Dev', 'Tsc', 'TD', 'Unkn')
 ) AS b

I have not tested it, but believe it will work

edited Jun 17 '13 at 22:12

answered Jun 17 '13 at 05:28

cha

10,301
1
18
26

Thanks cha, it worked out for the rows total. Still Iam trying for columns total – user1931944 Jun 17 '13 at 05:52
what do you mean? doesn't it calculate properly? – cha Jun 17 '13 at 06:00
It only calculates the row wise total alone but i need column wise total as wel Cha. – user1931944 Jun 17 '13 at 06:05
I see what you mean. you do this type of tricks with union. give me a sec and I'll update the answer – cha Jun 17 '13 at 06:37
Thanks Cha, but it is giving error while running the query at b. It is expecting some condition between a and b – user1931944 Jun 17 '13 at 07:37
You are right. You do not need the GROUP BY statement with the last Total Row/Column calculation. – cha Jun 17 '13 at 22:13
BTW, next time please provide some sample data. That way it is much easier to create a SQL Fiddle and test the queries there. As I said the queries are untested. In fact, you could even test them yourself and correct if required – cha Jun 17 '13 at 22:14

score 0 · Answer 4 · answered Jan 26 '22 at 17:30

You can also put you select pivot, without totals, in a temp table and then add the totalS with a new select:

SELECT  environment_name=ISNULL(environment_name, ‘Total’) , Enviro=SUM(Enviro), Requi=SUM(Requi),  Dev=SUM(Dev), TSc=SUM(TSc),  TD=SUM(TD), Unkn =SUM(Unkn),
        Total = sum(Enviro+Requi+Dev+TSc+TD+Unkn)
    FROM #temp
    GROUP BY ROLLUP(environment_name)

Using pivot table with column and row totals in sql server 2008

4 Answers4

Linked