处理空值，pivot中的动态列 [英] Dealing with nulls, dynamic columns in pivot

问题描述

你有数据集中的空值，我正试图用总列进行数据透视，因此我的总列出现为空。我的代码如下

Hi have nulls in data set that I'm trying to pivot with a total column and hence my total column is coming out as null. My code is as follows

DECLARE @cols       NVARCHAR(MAX)=''
DECLARE @query      NVARCHAR(MAX)=''
DECLARE @COLS_SUM   NVARCHAR(MAX)=''

--Preparing columns for Pivot
SELECT @cols = @cols + QUOTENAME(finmonth) + ',' 
FROM (SELECT DISTINCT finmonth FROM [DATASOURCE] ) AS tmp
SELECT @cols = SUBSTRING(@cols, 0, LEN(@cols))

--Preparing sum of columns for Totals Horizontal
SELECT @COLS_SUM = @COLS_SUM + QUOTENAME(finmonth) + '+' 
FROM (SELECT DISTINCT finmonth FROM [[DATASOURCE] ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'


SET @query = 
            'SELECT *'+@COLS_SUM+'  INTO #TAB FROM 
            (
                    SELECT var1, var2, var3, finmonth, sum(Actual_Activity) as [activity]
  FROM [[DATASOURCE]
  where [FinYear] = ''2018''
    group by  var1, var2, var3, finmonth
            ) src
            PIVOT 
            (
                   SUM([activity])  FOR finmonth IN (' + @cols + ')
            ) piv

            SELECT * FROM #TAB
            

            '

execute(@query)

以下是一些导致问题的虚拟数据示例。因此，一个Var1（下面的var1 = a）有finmonth = 2的数据但另一个没有。对于finmonth = 2，Var1 = b将为null，因为它们本月没有数据。

Here is some example dummy data which would result in the issue. So where one Var1 (var1=a in below) has data for finmonth=2 but another doesn't. Var1 = b will have null for finmonth = 2 as they have no data for this month.

var1	var2	var3	finmonth	Actual_activity
a	b	c	1	20
a	b	c	1	30
a	b	c	1	40
a	b	c	2	10
a	b	c	2	20
a	b	c	2	30
b	b	c	1	1
b	b	c	1	2
b	b	c	1	3

结果表如下所示 - 但第2行的总数应为6

The results table looks like this - but total for 2nd row should be 6

r1	var2	var3	1	2	Total
a	b	c	90	60	150
b	b	c	6	Null	Null

我尝试了什么：



我尝试在主要组中通过查询使用isnull但是似乎没有效果

What I have tried:

I've tried using isnull in the main group by query but seems to have no effect

推荐答案

sql语句的静态版本应如下所示：

A static version of sql statement should look like:

SELECT var1, var2, var3, [1], [2], COALESCE([1], 0) + COALESCE([2], 0) AS Total
FROM (
    SELECT var1, var2, var3, finmonth, Actual_Activity
    FROM YOUR_TABLE
    WHERE FinYear = 2018) AS DT
PIVOT(SUM(Actual_Activity) FOR finmonth IN ([1], [2])) AS PVT

现在，您必须将其转换为动态版。

如果您想避免 NULL 在 [1] 和 [2] cols，你可以使用 COALESCE 围绕他们的方法。



注意： FinYear 和 finmonth 应该是数字字段！





如果您想在 @COLS_SUM 变量中获取正确的数据，检查一下：

Now, you have to "convert it" into dynamic version.
If you would like to avoid NULL's in [1] and [2] cols, you can use COALESCE method around them.

Note: FinYear and finmonth should be numeric fields!


If you would like to get correct data in a @COLS_SUM variable, check this:

SELECT @COLS_SUM = @COLS_SUM + 'COALESCE(' + QUOTENAME(finmonth) + ',0)+' 
FROM (SELECT DISTINCT finmonth FROM YOUR_TABLE ) AS tmp
SELECT @COLS_SUM = ','+ SUBSTRING(@COLS_SUM, 0, LEN(@COLS_SUM)) +' AS TOTAL'

这篇关于处理空值，pivot中的动态列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！