如何对透视列的值求和并将其添加到另一个透视列中

本文介绍了如何对透视列的值求和并将其添加到另一个透视列中的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我想总结所有 CountHours 并显示在透视列 Total 中.Total 是所有 SUnday 、monday ... 的总和，对于特定的 UserName.如何实现这一目标?

I want to sum up all the CountHours and display in pivoted column Total.Total is the sum of all SUnday , monday ... for particular UserName. How to achieve this ?

select FullName,Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Total from (Select UserId_Fk,ISNULL(CAST(CountHours as decimal(18,2)),0)as CountHours,[Day] f rom CheckInCheckOut) as convertedtable inner join Users on convertedtable.UserId_Fk=Users.UserId PIVOT ( SUM(CountHours) FOR Day IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday],[Total]) ) as PivotTable

这个查询的结果是:

表格结构:

Table[CheckInCheckOut] CheckInCheckOutId int UserId_Fk int CountHours nvarchar(50) Day nvarchar(50)

示例将不胜感激.

推荐答案

你应该计算总列字段，即它不在数据透视列的列表中.

you should calculate total column field, i.e it is not in list of pivot columns.

数据

create table #CheckInCheckOut(Id int identity(1,1),UserId_Fk int,CountHours varchar(50),[Day] varchar(50)) INSERT INTO #CheckInCheckOut(UserId_Fk,CountHours,[Day]) VALUES (1,'2','Sunday'),(1,'2','Monday'),(1,'2','Tuesday'),(1,'2','Wednesday'),(1,'2','Thursday'),(1,'2','Friday'),(1,'2','Saturday') ,(2,'3','Sunday'),(2,'3','Monday'),(2,'3','Tuesday'),(2,'3','Wednesday'),(2,'3','Thursday'),(2,'3','Friday'),(2,'3','Saturday') ,(3,'3','Sunday'),(3,'3','Monday'),(3,'3','Tuesday'),(3,'3','Wednesday'),(3,'3','Thursday'),(3,'3','Friday'),(3,'3','Saturday') create table #Users(UserId int identity(1,1),FullName varchar(50)) INSERT #Users(FullName) values('Abdul'),('khan'),('Tariq')

查询也可以找到总数:

select FullName ,[Sunday] = SUM([Sunday]) ,[Monday] = SUM([Monday]) ,[Tuesday] = SUM([Tuesday]) ,[Wednesday] = SUM([Wednesday]) ,[Thursday] = SUM([Thursday]) ,[Friday] = SUM([Friday]) ,[Saturday] = SUM([Saturday]) , Total= SUM([Sunday]+[Monday]+[Tuesday]+[Wednesday]+[Thursday]+[Friday]+[Saturday]) from (Select UserId_Fk,ISNULL(CAST(CountHours as decimal(18,2)),0)as CountHours,[Day] from #CheckInCheckOut) as convertedtable inner join #Users on convertedtable.UserId_Fk=#Users.UserId PIVOT ( SUM(CountHours) FOR Day IN([Sunday],[Monday],[Tuesday],[Wednesday],[Thursday],[Friday],[Saturday]) ) as PivotTable GROUP BY FullName

输出

另外，如果你想要总水平和垂直，然后替换:

Also if u want total horizontal and vertical both then replace:

--GROUP BY FullName GROUP BY ROLLUP(FullName);

更多请关注链接stackoverflow/a/17142530/1915855

DROP TABLE #CheckInCheckOut DROP TABLE #Users