如何获取动态透视变量的总数？ [英] How to take a total for dynamic pivot variable?

问题描述

大家好，

i有点混淆动态pivot sql，

如何获取动态数据透视变量的总数？

i尝试了以下查询，但它说，

'SNULL'不是公认的内置函数名称。

请给我一个解决方案或者给我一些想法。< br $>

Hi all,
i little confusing about dynamic pivot sql,
How to take a total for dynamic pivot variable?
i tried by the below query but its saying that ,
'SNULL' is not a recognized built-in function name.
please drop me a solution or else give me an some idea.

DECLARE @Scolumns NVARCHAR(MAX)
DECLARE @columns NVARCHAR(MAX)
 
SELECT   @scolumns = ISNULL(@Scolumns + ',','')+ 'ISNULL(' + QUOTENAME(Name) + ', 0) AS '+ QUOTENAME(Name)
FROM (
	SELECT Name  FROM #Temp
) AS x;
 
SELECT   @columns = ISNULL(@columns + ',','')+ QUOTENAME(Name) 
FROM (
	SELECT Name  FROM #Temp
) AS x;
 
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'SELECT ' + @Scolumns + ',
  ' + STUFF(replace(@Scolumns,',','+'), 1, 2, '') + ' as Total,  -- HERE AM GETTING ERROR
FROM
(
  SELECT SaleQuantity,CONVERT(VARCHAR,YEAR(MonthOf))+''_''+DATENAME(MM,MonthOF) AS MonthNames  FROM TEST (NOLOCK)
  
) AS j
PIVOT
(
  SUM(SaleQuantity) FOR MonthNames IN (' + @columns  + ')
) AS p;';
PRINT @sql;

提前感谢



我尝试过：



如何获取动态数据透视变量的总数？

thanks in advance

What I have tried:

How to take a total for dynamic pivot variable?

推荐答案

我已经告诉过你如何在你早期的帖子中解决这个问题如何在动态数据透视中将空值替换为0 [ ^ ]。



摆脱 STUFF - 你已经创建了一个以逗号分隔的列列表（或者是更准确的人已经为你完成了这个。）



使用手动编辑的查询，直到你可以使用一些值，然后将其转换为动态SQL 。



实际上看看

I already told you how to fix this on your earlier post How to replace null values to 0 in dynamic pivot[^].

Get rid of the STUFF - you have already created a comma-separated list of columns (or to be more accurate someone has already done this for you).

Use a manually edited query until you can get it to work with a few values, then convert it to dynamic SQL.

Actually LOOK at the output from

PRINT @sql;

你会看到它是无效的sql。

and you will see that it is not valid sql.

这篇关于如何获取动态透视变量的总数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！