Pivot row totals - unpivot?

Question

Post reply

Pivot row totals - unpivot?

as_1234

SSCrazy

Points: 2842
More actions
January 4, 2018 at 10:17 am

#342275

All:
I have the following test setup:
DDL:

CREATE TABLE [dbo].[tabPivot](
[ContactType] [varchar](50) NULL,
[LogDate] [numeric](18, 0) NULL,
[Total] [numeric](18, 0) NULL)
GO
DML:

Insert into tabPivot(contacttype,logdate,total) values ('Account',1,50)
Insert into tabPivot(contacttype,logdate,total) values ('Account',2,3)
Insert into tabPivot(contacttype,logdate,total) values ('App',1,4)
Query:
set dateformat dmy
select * from (select isnull(contacttype,'Report total') as title,isnull(cast(logdate as varchar),'Topic total') as logdate,sum(total) as total from tabpivot as src
group by contacttype,logdate) as src
PIVOT (sum(total) for logdate IN ([1],[2])) prv
order by title
This gives me a row for each contact type and a column for each logdate which is what I expected.
I'm trying to work out how to add a total row. From what I've read I should be able to do this with unpivot? If so then I'm struggling to work out the correct unpivot syntax for this example. Everything I try results in errors. I can't work out whether unpivot is the wrong command or whether I'm just not using it correctly?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Perhaps it is simpler to avoid PIVOT/UNPIVOT altogether.

Something like this approach might work for you:

SELECT title =ISNULL(contacttype, CASE WHEN GROUPING(contacttype)=0 THEN NULL ELSE 'Total' END) ,[1] =SUM(CASE WHEN logdate=1 THEN total END) ,[2] =SUM(CASE WHEN logdate=2 THEN total END) FROM tabpivot GROUP BY GROUPING SETS ((contacttype),()) ;

Cheers!

as_1234 SSCrazy Points: 2842 More actions · Answer 2

Hello,

Thank you for the suggestion. In the full version the pivot columns are created with dynamic sql so the pivot will be easier if I can get it to work.

I have got closer. I now have:

set dateformat dmy

select * from (select isnull(contacttype,'Report total') as title,isnull(cast(logdate as varchar),'Topic total') as logdate,sum(total) as total from tabpivot as src group by contacttype,logdate with rollup) as src piVOT (sum(total) for logdate IN ([1],[2],[Topic Total])) prv unpivot (total for logdate in ([Topic Total])) prv2 order by title

This adds a total column which has the right values but for some reason it's not transforming this into a row which I was expecting the unpivot to do and I can't work out why. It also adds a column 'logdate'. I'm using the example here

http://www.sqlservercentral.com/blogs/vivekssqlnotes/2012/04/14/pivot-and-unpivot-table-in-sql-server/

and trying to match the columns between the example table and mine.

I matched total with mark and logdate with grade. In the unpivot 'in' I only specified topic total as that is the only column that I want to move to a row.

Thanks