如何对透视列的值求和并将其添加到另一个透视列中 [英] 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屋！