Using Pivot Table with Column and Row Totals in SQL Server 2008

  • Home
  • Languages
  • SQL
  • Using Pivot Table with Column and Row Totals in SQL Server 2008

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

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:

  • Method 1

  • Method 2

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.

Row and column total in dynamic pivot

SAMPLE TABLE

SELECT * INTO #tblStock
FROM
(
SELECT 'A' PartCode,  10 StockQty, 'WHs-A' Location
UNION ALL
SELECT 'B',   22,  'WHs-A'
UNION ALL
SELECT 'A',   1,   'WHs-B'
UNION ALL
SELECT 'C',   20,  'WHs-A'
UNION ALL
SELECT 'D',   39,  'WHs-F'
UNION ALL
SELECT 'E',   3,   'WHs-D'
UNION ALL
SELECT 'F',   7,   'WHs-A'
UNION ALL
SELECT 'A',   9,   'WHs-C'
UNION ALL
SELECT 'D',   2,   'WHs-A'
UNION ALL
SELECT 'F',   54,  'WHs-E'
)TAB

Get the columns for dynamic pivoting and replace NULL with zero

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + Location + ']', '[' + Location + ']')
               FROM (SELECT DISTINCT Location FROM #tblStock) PV 
               ORDER BY Location 
-- Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'

--Varible to replace NULL with zero
DECLARE @NulltoZeroCols NVARCHAR (MAX)

SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+Location+'],0) AS ['+Location+']' 
FROM (SELECT DISTINCT Location FROM #tblStock)TAB  
ORDER BY Location FOR XML PATH('')),2,8000) 

SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'

You can use CUBE to find row and column total and replace NULL with Total for the rows generated from CUBE. 

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT PartCode,' + @NulltoZeroCols + ' FROM 
             (
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode, 
                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblStock
                 GROUP BY Location,PartCode
                 WITH CUBE
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode' 

EXEC SP_EXECUTESQL @query
  • Click here to view result

RESULT

Sample Image

NOTE : If you want NULL instead of zero as values, use @cols instead of @NulltoZeroCols in dynamic pivot code

EDIT :

1. Show only Row Total

  • Do not use the code SELECT @cols += ',[Total]' and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'.
  • Use ROLLUP instead of CUBE.

Sample Image

2. Show only Column Total

  • Use the code SELECT @cols += ',[Total]' and SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'.
  • Use ROLLUP instead of CUBE.
  • Change GROUP BY Location,PartCode to GROUP BY PartCode,Location.
  • Instead of ORDER BY CASE WHEN (PartCode=''Total'') THEN 1 ELSE 0 END,PartCode, use WHERE PartCode<>''TOTAL'' ORDER BY PartCode.

Sample Image

UPDATE : To bring PartName for OP

I am updating the below query to add PartName with result. Since PartName will add extra results with CUBE and to avoid confusion in AND or OR conditions, its better to join the pivoted result with the DISTINCT values in your source table.

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT P.PartCode,T.PartName,' + @NulltoZeroCols + ' FROM 
             (                
                 SELECT 
                 ISNULL(CAST(PartCode AS VARCHAR(30)),''Total'')PartCode, 
                 SUM(StockQty)StockQty , 
                 ISNULL(Location,''Total'')Location              
                 FROM #tblStock
                 GROUP BY Location,PartCode
                 WITH CUBE                   
             ) x
             PIVOT 
             (
                 MIN(StockQty)
                 FOR Location IN (' + @cols + ')
            ) p
            LEFT JOIN
            (  
                SELECT DISTINCT PartCode,PartName
                FROM #tblStock  
            )T
            ON P.PartCode=T.PartCode
            ORDER BY CASE WHEN (P.PartCode=''Total'') THEN 1 ELSE 0 END,P.PartCode' 

EXEC SP_EXECUTESQL @query
  • Click here to view result

Sample Image

SQL pivot table with column and row totals

Here you try.

WITH T AS (
            SELECT 
                  A.city as city, A.sex as sex,  
                  CASE
                        WHEN A.age BETWEEN 20 AND 30 THEN '20-30_' + race
                        WHEN A.age BETWEEN 31 AND 40 THEN '31-40_' + race
                        WHEN A.age BETWEEN 41 AND 50 THEN '41-50_' + race
                   END AS age_range_race
           FROM #test4 AS A
           )

SELECT  *, ([20-30_African-American] + [20-30_Asian] + [20-30_Caucasian]+ [20-30_Hispanic]+ [31-40_African-American]+ [31-40_Asian]+ [31-40_Caucasian]+ [31-40_Hispanic]+[41-50_African-American]+ [41-50_Asian]+ [41-50_Caucasian]+ [41-50_Hispanic]) Total
into #tmp_result
FROM T
PIVOT( COUNT(age_range_race) FOR age_range_race
   IN(
      [20-30_African-American], 
      [20-30_Asian], 
      [20-30_Caucasian], 
      [20-30_Hispanic],
      [31-40_African-American], 
      [31-40_Asian], 
      [31-40_Caucasian], 
      [31-40_Hispanic],
      [41-50_African-American], 
      [41-50_Asian], 
      [41-50_Caucasian], 
      [41-50_Hispanic]
     )
) AS P

select * 
from #tmp_result
union all
select 'Grand Total','',SUM([20-30_African-American]), SUM([20-30_Asian]), SUM([20-30_Caucasian]), SUM([20-30_Hispanic]),SUM([31-40_African-American]), SUM([31-40_Asian]), SUM([31-40_Caucasian]), SUM([31-40_Hispanic]),SUM([41-50_African-American]), SUM([41-50_Asian]), SUM([41-50_Caucasian]),SUM([41-50_Hispanic]), sum(Total)
from #tmp_result

Add A Total Row To Bottom Of Pivot

UNION ALL with an aggregated row will show you the raw pivoted data with the SUMs

WITH data AS (
    select *
    FROM
    (
      select case 
      WHEN a.state LIKE 'CA' THEN 'California'
      WHEN a.state LIKE 'WA' THEN 'Washington'
      else a.state
      end As [Full State], 
      SaleID As [Sales By State], 
      CONVERT(VARCHAR(20), dt.CumulativeWeek) AS Week
      FROM retailsales.Store1 a
      INNER JOIN retailsales.customCalendar dt
      ON a.orderDate = dt.orderDate
      WHERE a.orderDate IS NOT NULL
      GROUP BY CUBE (SaleID, state, dt.CumulativeWeek)
    ) src
    pivot
    (
      COUNT([Sales By State])
      For Week IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13])
    ) piv
)
SELECT [Full State]
     , [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13]
FROM   data
UNION ALL
SELECT ''
     , SUM([1]),SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6])
     , SUM([7]),SUM([8]),SUM([9]),SUM([10]),SUM([11]),SUM([12]),SUM([13])
FROM   data

SQL Server Row totals in pivot query

If I understand this correctly you want to count all columns which are not null. In this case you should just look at the condition IS NULL and not at the actual value at all. Try this:

DECLARE @tbl TABLE(ID INT IDENTITY, val1 VARCHAR(100),val2 VARCHAR(100),val3 VARCHAR(100));

INSERT INTO @tbl VALUES
 ('row1_val1','row1_val2',NULL)
,('row2_val1','row2_val2','row2_val3')
,(NULL,'row2_val2',NULL)
,(NULL,NULL,'row2_val3')
,(NULL,NULL,NULL);

SELECT *
      ,CASE WHEN val1 IS NULL THEN 0 ELSE 1 END 
      +CASE WHEN val2 IS NULL THEN 0 ELSE 1 END
      +CASE WHEN val3 IS NULL THEN 0 ELSE 1 END AS CountOfValNotNull
FROM @tbl

UPDATE: Add a final Totals Row

You'd need ugly fiddling with a CTE, an additional sort column, UNION ALL to add another row and a sub_select.

Use the outer-most ORDER BY to get the artificial Totals-Row to the end

hint: Use the @tbl variable from above!

WITH SortedRows AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY ID) AS SortColumn
          ,*
          ,CASE WHEN val1 IS NULL THEN 0 ELSE 1 END 
          +CASE WHEN val2 IS NULL THEN 0 ELSE 1 END
          +CASE WHEN val3 IS NULL THEN 0 ELSE 1 END AS CountOfValNotNull
    FROM @tbl
)

SELECT tbl1.*
FROM
(
    SELECT * FROM SortedRows
    UNION ALL
    SELECT 999999,0,'','','',(SELECT SUM(CountOfValNotNull) FROM SortedRows)
) AS tbl1
ORDER BY tbl1.SortColumn

Column Total at the end of SQL Pivot query

The easiest solution would be to simply do something like this:

Select *,
    Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec AS [Total]
from
    ...

An alternative solution for the general case, would be to use a subselect. Move your inner query into a CTE, to make things a bit easier to work with:

WITH POnumber (POStatus, [Year], [poyear], [pomonth]) AS
(
    Select sase when (podocstatus = 'CL') then 'Closed PO'
         when (podocstatus = 'OP') then 'Open PO'
         when (podocstatus = 'SC') then 'Short Closed PO'   
    end as POStatus, 
    YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
    From PO_order_hdr
    Where podocstatus IN ('SC','CL','OP')
)
select *,
    -- Subselect that counts the total for the given status and year:
    (select count([Year]) from POnumber T 
     where T.POStatus = PVT.POStatus and T.poyear = PVT.poyear) as [Total]
from POnumber
PIVOT
(
    Count(poyear)
    FOR [pomonth]  IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
)as PVT

SQL Dynamic Pivot table with ROW and Column Total

Assumption :

  • fy_week is a string data type

to obtain the columnwise total, add to your query x

from 
( 
    -- your original query
    select E_ID, Full_name, Dept, fy_week, fy_rev -- you missed the fy_week & fy_rev here
    from   my_sample_table 

    -- add the following few lines : union all & select query
    union all

    select E_ID, Full_name, Dept, fy_week = ''Total'', fy_rev = sum(fy_rev)
    from   my_sample_table 
    group by E_ID, Full_name, Dept
) x

and the @cols will need to appended with column name Total. Add below to after your set @cols query

select  @cols = '[Total],' + @cols

for the line level query, you will need another query which is group by fy_week, for this, i have make use of CTE as you need to reference the above x query twice

the complete query. (i reformatted it a bit for my eyes)

DECLARE 
    @cols   AS NVARCHAR(MAX), 
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(fy_week) y
            FROM my_sample_table z
            ORDER BY y asc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')

SET @cols = '[Total],' + @cols           -- added this line

-- added cte query
SET @query  = '
    ; with cte as
    (
            select  E_ID, Full_name, Dept, 
                    fy_week = convert(varchar(10), fy_week), fy_rev 
            from    my_sample_table 

            union all

            select  E_ID, Full_name, Dept, 
                    fy_week = ''Total'', fy_rev = sum(fy_rev) 
            from    my_sample_table 
            group by E_ID, Full_name, Dept
    )
    SELECT E_ID, Full_name, Dept, ' 
        + @cols + ' 
    from 
    ( 
        select  E_ID, Full_name, Dept, fy_week, fy_rev 
        from    cte

        -- the following is for row wise total
        union all

        select  E_ID = 99, Full_name = ''Total'', Dept = '''', fy_week, sum(fy_rev) 
        from    cte 
        group by fy_week
    ) x
    pivot 
    ( 
        Sum(fy_rev) 
        for fy_week in (' + @cols + ')
    ) p '

-- print out to validate
print   @query
execute(@query)

EDIT : change to handle fy_week is an integer column

Calculating Horizontal Totals in Pivot in MS SQL Server 2008

One way of doing it

SELECT CASE WHEN GROUPING([FCode]) = 1 THEN 'Total' ELSE [FCode] END AS [FCode],
       SUM([DMAR15]) AS DMAR15,
       SUM([DMAR02]) AS [DMAR02]
        /*TODO: Rest of columns*/
FROM   (SELECT [FCode],
               [Aggregate],
               [QName]
        FROM   [tblMiquestResults]) AS SourceTable 
PIVOT (AVG (Aggregate) FOR [QName] IN ([DMAR15], [DMAR02], [DMAR13], 
                                       [DMAR06], [PCVDR41], [PCVDR42], 
                                       [CLDP031], [CLDP003], [CLDP012], 
                                       [CLDP028], [CLDP023], [CLDP021], 
                                       [CLDP016], [CLDP022])) AS P 
GROUP BY GROUPING SETS ((FCode),())

SQL Fiddle


Related Topics

How to Add a Column and Make It a Foreign Key in Single MySQL Statement

Get All Characters Before Space in MySQL

How to Insert Data into Table Using Stored Procedures in Postgresql

SQL Select Rows with Only a Certain Value in Them

How to Select Columns from a Table Which Have Non Null Values

How to Correctly Do Upsert in Postgres 9.5

JSONb Query with Nested Objects in an Array

Is Null VS = Null in Where Clause + SQL Server

How to Write a Function in the H2 Database Without Using Java

How to Insert Multiple Rows with a Foreign Key Using a Cte in Postgres

How to Do a Simple 'Find and Replace" in Mssql

Row_Number Without Order By

How to Make a Table Read Only in SQL Server

Using Input from a Text File for Where Clause

How to Convert Date to a Format 'Mm/Dd/Yyyy'

How to Create Sequence If Not Exists

Delete Command Is Too Slow in a Table with Clustered Index

SQL How to Convert Row with Date Range to Many Rows with Each Date


Leave a reply



Submit