SQL Server中PIVOT表中动态列的SUM [英] SUM of dynamic Columns in PIVOT table in SQL Server

问题描述

我有一个动态PIVOT查询，其中列是动态生成的。



我的表：ATTENDANCE_MASTER包含：ID，Stud_id，ATT_DATE，PRESENT



存储数据如：

I have a Dynamic PIVOT query in which Columns are dynamically generated.

My table: ATTENDANCE_MASTER Contains: ID, Stud_id, ATT_DATE, PRESENT

which stores data like:

ID  Stud_id ATT_DATE   PRESENT
1     1     2015-08-1    1
2     2     2015-08-1    0
3     3     2015-08-1    1
4     1     2015-08-2    0
5     2     2015-08-2    1
6     3     2015-08-2    1

我创建了PIVOT查询

I have created PIVOT Query

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(ATT_DATE)
  FROM (SELECT p.ATT_DATE FROM dbo.ATTENDANCE_MASTER AS p
  GROUP BY p.ATT_DATE) AS x;

SET @sql = N'SELECT Stud_id, ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.ATT_DATE, p.Stud_id, p.PRESENT FROM dbo.ATTENDANCE_MASTER AS p
) AS j
PIVOT
(
  SUM(PRESENT) FOR ATT_DATE IN ('+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '') + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;

我需要列的总和如

I need sum of columns like

Stud_ID  2015-08-01   2015-08-2 2015-08-3 Total
1            1            0         1      2
2            1            1         1      3
3            1            1         0      2
4            0            0         1      1

请建议我解决方案。



先谢谢。

Please suggest me solution.

Thanks in Advance.

推荐答案

你好，

参考：

SQL - 带有总列和行的Pivot

逻辑是：

The logic is:

SELECT T.*, ([2015-08-01] + [2015-08-02] + [2015-08-03]) AS Total
FROM 
(
-- your dynamic query
) AS T

其中 [2015-08-01] + [2015-08-02] + [2015-08-03] 来自的注释@columns 变量;）



得到它？

where [2015-08-01] + [2015-08-02] + [2015-08-03] commes from @columns variable ;)

Get it?

我最近写了一篇文章谈论这个。您可以在将PIVOT与SQL Server一起使用 [ ^ ]



我希望它适合你！

Hi, I recently wrote an article talking about this. You can check it at Using PIVOT with SQL Server[^]

I hope it works for you!

这篇关于SQL Server中PIVOT表中动态列的SUM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！