SQL获取数据透视表的列值的平均值 [英] SQL Get the Average of the Column Values of a Pivot

问题描述

你好，



遇到SQL pivot问题。下面是我运行查询时得到的实际结果。

Hi There,

Having trouble with SQL pivot. below is the actual result i get when i ran the query.

Name | Average | 7/24/2014 | 7/26/2014    | 7/27/2014      | 7/28/2014    | 7/31/2014
Test | -----------| NULL      | 00:06:20:000 |	00:06:24:000 | 00:06:25:000 | 00:06:24:000

现在，我想知道的是我如何添加每列的值，计算显示平均值。
$如果想要的输出，请低于b $ b。

Now, what i want to know is how do i add the values of each column and compute show the average.
below if the desired output.

Name | Average | 7/24/2014 | 7/26/2014    | 7/27/2014      | 7/28/2014    | 7/31/2014
Test | 00:06:23:000| NULL      | 00:06:20:000 |  00:06:24:000 | 00:06:25:000 | 00:06:24:000

以下是Pivot的代码

below is the code of Pivot

BEGIN
     SET @query = ' SELECT * FROM(SELECT p.[Name]
     , ''-----------'' AS Average 
     , CONVERT(varchar, DATEADD(ms, AVG(p.CallLengthSec) * 1000, 0), 114) AS CallLengthSumMin
     , p. ' + @dategroup + ' 
FROM #prod p WHERE p.EmpInfoID IN (' + @ProfGroupIDs + ') ' + @withinshift + ' 

GROUP BY p.Name, p. ' + @dategroup + '   ) x
PIVOT ( MAX(CallLengthSumMin) FOR ' + @dategroup + ' IN (' + @cols + ') ) p  
ORDER BY p.Name ASC '
END

先谢谢。谢谢

Thanks, in advance. Thanks

推荐答案

我假设您为每个日期和整个列的平均值获得 CallLengthSumMin 的平均值：

I asssume that you wat to get average of CallLengthSumMin for each date and average for entire column:

date1    average
date2    average
date3    average
...
dateN    average
-----    total average

然后你想把数据作为数据透视表来显示。



如果我是没错，请参考这个 [ ^ ]

这篇关于SQL获取数据透视表的列值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！