January 4, 2018 at 10:17 am
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
January 4, 2018 at 10:30 am
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!
January 4, 2018 at 2:05 pm
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply