如何对透视列的值求和并将其添加到另一个透视列中 [英] How to Sum value of Pivoted Columns and add it into another Pivoted Column
本文介绍了如何对透视列的值求和并将其添加到另一个透视列中的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想总结所有 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);
更多请关注链接https://stackoverflow.com/a/17142530/1915855
DROP TABLE #CheckInCheckOut
DROP TABLE #Users
这篇关于如何对透视列的值求和并将其添加到另一个透视列中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文