How do I create a "Total" column and footer from a pivoted table?

Question

I have an inventory table with warehouse, product, and available columns which looks like:

warehouse    product    available
John2196     KITCOMP01    7
John2196     KITCOMP01    12
John2196     KITCOMP02    7
JohnS196     KITCOMP01    9
JohnS196     KITCOMP03    1

And the warehouse column is pivoted to create this:

product    John2196    JohnS196
KITCOMP01     19          9
KITCOMP02     7          NULL
KITCOMP03    NULL         1

with this code

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

select @cols = STUFF((SELECT ',' + QUOTENAME(Warehouse) 
                from tlninventory
                group by Warehouse
                order by Warehouse
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT product,' + @cols + ' from 
         (
            select product, warehouse, available
            from tlninventory
        ) x
        pivot 
        (
            sum(available)
            for warehouse in (' + @cols + ')
        ) p '

execute(@query)

Now I need to get a Total Column and Total Footer Row while ignoring NULL values but since the headers are created through the pivot and this query will be run on multiple tables with differing warehouses and products, meaning the headers will never be the same, I can not sum static columns such as this thread suggests.

For reference I need a final table that looks like:

product    John2196    JohnS196    Total
KITCOMP01     19          9         28
KITCOMP02     7          NULL       7
KITCOMP03    NULL         1         1
Total         26          10        36

check out rollup, cube, and grouping sets https://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx — Jeremy, Nov 02 '15 at 19:46

JamieD77 · Accepted Answer · 2015-11-02T20:37:15.813

You need to do a few things here.

First create a new variable to store the 'sum(warehouse)' columns

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

Create the sum columns similar to the regular pivot column names

select @sumCols = STUFF((SELECT ',' + 'SUM(' + QUOTENAME(Warehouse) + ')' + QUOTENAME(Warehouse)
                from tlninventory
                group by Warehouse
                order by Warehouse
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

now you need to create a cte of your pivot query, but add a windowed sum() over() to get the row totals..

set @query = ' WITH cte AS  
        (
            SELECT product,' + @cols + ', Total from 
             (
                select product, warehouse, available, sum(available) over (partition by product) total
                from tlninventory
            ) x
            pivot 
            (
                sum(available)
                for warehouse in (' + @cols + ')
            ) p 
        )

now you Union a total column at the end using your sum columns .

set @query = ' WITH cte AS  
        (
            SELECT product,' + @cols + ', Total from 
             (
                select product, warehouse, available, sum(available) over (partition by product) total
                from tlninventory
            ) x
            pivot 
            (
                sum(available)
                for warehouse in (' + @cols + ')
            ) p 
        ) 
        SELECT product,' + @cols + ', Total FROM (
            SELECT *, ''a'' sortCol 
            FROM cte 
            UNION ALL 
            SELECT ''Total'', ' + @sumCols + ', sum(total), ''z''
            FROM cte 
        ) a
        ORDER BY sortCol, product'

execute(@query)

SQL Fiddle

How do I create a "Total" column and footer from a pivoted table?

1 Answers1