SQL pivot查询结束时的列总数 [英] Column total at the end of SQL pivot query

问题描述

我已经生成了以下SQL Server数据透视表，它给了我渴望的结果。我想在枢轴末端添加总列，我觉得很难。



请找到我用于枢轴的SQL，

I have generated the following SQL Server pivot, and it gives me desire result. I want to add total column at end of pivot, where I am finding it difficult.

Please find the SQL I'm using for pivot,

DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT   @columns =  @columns+N', p.' +QUOTENAME(Name)
  FROM (
		SELECT distinct Name  FROM #Temp2

   ) AS x;
SET @sql = N'
SELECT 
        P.PNAME,
	
SUM(' + @columns + ') AS TOTAL,  -- here am getting error like incorrect syntaxt near ','
 ' + STUFF(@columns, 1, 2, '') + '
FROM
(
             SELECT P.ProductCode,	
				
				
	 CONVERT(VARCHAR,YEAR(PT.BILLDATE))+''_''+DATENAME(MM,PT.BILLDATE) AS MonthNames  
              FROM TEST PT (NOLOCK)
                    INNER JOIN  Products P (NOLOCK) ON PT.PNAME=P.PNAME

  
) AS j
PIVOT
(
  SUM(SaleQuantity) FOR MonthNames IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';

PRINT @sql;
EXEC sp_executesql @sql;

DROP TABLE #Temp2

提前感谢



我尝试过：



SQL Pivot查询结尾处的列总数

thanks in advance

What I have tried:

Column Total at the end of SQL Pivot query

推荐答案

下面是使用Microsoft Northwind数据库的示例。我在 Orders 表上创建了一个简单的透视图，如下所示

Here is an example using the Microsoft Northwind database. I created a simple pivot on the Orders table as follows

SELECT CustomerID,	ROW_NUMBER() OVER (ORDER BY CustomerID) as rn,
			ISNULL([1996],0) as [1996],
			ISNULL([1997],0) as [1997],
			ISNULL([1998],0) as [1998]
INTO #temp
FROM 
(
	SELECT CustomerID, YEAR(OrderDate) as Y, Freight
	FROM ORDERS
	WHERE CustomerID IN ('ALFKI','BLAUS','DRACD')
) source
PIVOT
(
	SUM(Freight) FOR Y IN ([1996],[1997],[1998])
) pvt

注意事项 - 我已将PIVOT结果插入临时表（您可以轻松使用Table变量）。

我还包括一个行号。



结果是：

Points to note - I've inserted the PIVOT results into a temporary table (you can just as easily use a Table variable).
I've also included a row number.

The results of that are:

ALFKI	1	0.00	114.42	111.16
BLAUS	2	0.00	38.64	129.62
DRACD	3	35.99	33.35	236.70

我可以将其与总数联合起来，使用行号来制作确保总计最终在底部。

I can then union that with a total, using the row number to make sure the Total ends up at the bottom.

select CustomerID, [1996],[1997],[1998] from 
(
	select rn, CustomerID, [1996],[1997],[1998] FROM #temp
	UNION
	select MAX(rn) + 1, 'TOTAL', SUM([1996]),SUM([1997]),SUM([1998])
	from #temp
) source
ORDER BY rn

我在这里使用子查询，因为我不希望行号成为最终查询的一部分，但如果您只是忽略表示层中的行号，则不是必需的。



结果之后最终的UNION：

I use a sub-query here because I don't want the row number to be part of the final query, but it's not essential if you just ignore the row number in your presentation layer.

Results after the final UNION :

ALFKI	0.00	114.42	111.16
BLAUS	0.00	38.64	129.62
DRACD	35.99	33.35	236.70
TOTAL	35.99	186.41	477.48

请参阅我在CP上的示例，名为在SQL Server 2005,2008和2008 R2中使用多维数据集和事件处理程序进行动态透视 [ ^ ]。

See my example on CP called Dynamic Pivoting with Cubes and eventhandlers in SQL Server 2005, 2008 and 2008 R2[^].

这篇关于SQL pivot查询结束时的列总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！