如何将枢轴输出中的NULL值替换为零? [英] How to replace the NULL values in a pivot output with zeroes?
本文介绍了如何将枢轴输出中的NULL值替换为零?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在回答之后，我想摆脱在某些情况下我没有数字时会出现一些NULL值
Following the answer from a previous question I want to get rid of some NULL values when I do not have a number for some cases
我有以下查询
CREATE TABLE [MYTABLE]
(
[A] int
, [B] int
, [VAL] float
);
INSERT INTO [MYTABLE] ([A], [B], [VAL]) VALUES
(1, 1, -218.46),
(1, 2, -2.1846000000000e+02),
(2, 1, 6.4000000000000e+01),
(2, 2, -218.46),
(2, 3, 6.4000000000000e+01),
(3, 2, -2.1846000000000e+02),
(3, 3, -218.46),
(4, 3, 6.4000000000000e+01),
(4, 4, -218.46),
(4, 5, 6.4000000000000e+01),
(5, 4, 6.4000000000000e+01),
(5, 5, -218.46),
(5, 6, 6.4000000000000e+01),
(6, 5, 6.4000000000000e+01),
(6, 6, -218.46),
(6, 7, 6.4000000000000e+01),
(7, 6, 6.4000000000000e+01),
(7, 7, -218.46),
(7, 8, 6.4000000000000e+01),
(8, 7, 6.4000000000000e+01),
(8, 8, -218.46),
(8, 9, 6.4000000000000e+01),
(9, 8, 6.4000000000000e+01),
(9, 9, -218.46),
(9, 10, 6.4000000000000e+01),
(10, 9, 6.4000000000000e+01),
(10, 10, -218.46);
SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10]
FROM
( SELECT x = a
, y = b
, v = (CASE WHEN IsNumeric(val)=0 THEN 0 ELSE val END)
FROM MYTABLE
) AS T
PIVOT
(
min( v)
FOR y IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS pvt
order by x
我得到以下结果:
1 2 3 4 5 6 7 8 9 10
------- ------- ------- ------- ------- ------- ------- ------- ------- -------
-218.46 -218.46 NULL NULL NULL NULL NULL NULL NULL NULL
64 -218.46 64 NULL NULL NULL NULL NULL NULL NULL
NULL -218.46 -218.46 NULL NULL NULL NULL NULL NULL NULL
NULL NULL 64 -218.46 64 NULL NULL NULL NULL NULL
NULL NULL NULL 64 -218.46 64 NULL NULL NULL NULL
NULL NULL NULL NULL 64 -218.46 64 NULL NULL NULL
NULL NULL NULL NULL NULL 64 -218.46 64 NULL NULL
NULL NULL NULL NULL NULL NULL 64 -218.46 64 NULL
NULL NULL NULL NULL NULL NULL NULL 64 -218.46 64
NULL NULL NULL NULL NULL NULL NULL NULL 64 -218.46
但是我需要以下结果
1 2 3 4 5 6 7 8 9 10
------- ------- ------- ------- ------- ------- ------- ------- ------- -------
-218.46 -218.46 NULL NULL 0 0 0 0 0 0
64 -218.46 64 0 0 0 0 0 0 0
0 -218.46 -218.46 0 0 0 0 0 0 0
0 0 64 -218.46 64 0 0 0 0 0
0 0 0 64 -218.46 64 0 0 0 0
0 0 0 0 64 -218.46 64 0 0 0
0 0 0 0 0 64 -218.46 64 0 0
0 0 0 0 0 0 64 -218.46 64 0
0 0 0 0 0 0 0 64 -218.46 64
0 0 0 0 0 0 0 0 64 -218.46
如何将所有NULL值替换为0?
How do I replace all NULL values with 0's?
在@Siva回答之后
CREATE TABLE [dbo].[Matrix]
(
[A] INT
, [B] INT
, [VAL] FLOAT
);
INSERT INTO [dbo].[Matrix] ([A], [B], [VAL])
VALUES
(1, 1, -218.46)
, (1, 2, -2.1846000000000e+02)
, (2, 1, 6.4000000000000e+01)
, (2, 2, -218.46)
, (2, 3, 6.4000000000000e+01)
, (3, 2, -2.1846000000000e+02)
, (3, 3, -218.46)
, (4, 3, 6.4000000000000e+01)
, (4, 4, -218.46)
, (4, 5, 6.4000000000000e+01)
, (5, 4, 6.4000000000000e+01)
, (5, 5, -218.46)
, (5, 6, 6.4000000000000e+01)
, (6, 5, 6.4000000000000e+01)
, (6, 6, -218.46)
, (6, 7, 6.4000000000000e+01)
, (7, 6, 6.4000000000000e+01)
, (7, 7, -218.46)
, (7, 8, 6.4000000000000e+01)
, (8, 7, 6.4000000000000e+01)
, (8, 8, -218.46)
, (8, 9, 6.4000000000000e+01)
, (9, 8, 6.4000000000000e+01)
, (9, 9, -218.46)
, (9, 10, 6.4000000000000e+01)
, (10, 9, 6.4000000000000e+01)
, (10, 10, -218.46);
SELECT COALESCE([1], 0) [1]
, COALESCE([2], 0) [2]
, COALESCE([3], 0) [3]
, COALESCE([4], 0) [4]
, COALESCE([5], 0) [5]
, COALESCE([6], 0) [6]
, COALESCE([7], 0) [7]
, COALESCE([8], 0) [8]
, COALESCE([9], 0) [9]
, COALESCE([10], 0) [10]
FROM
(
SELECT x = a
, y = b
, v = val
FROM [dbo].[Matrix]
) AS dataToPivot
PIVOT
(
MIN(v)
FOR y IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) AS pivoted
ORDER BY x;
动态地做到这一点的最佳方法是什么，我当时正在考虑为第一部分做...:
What woul be the best way to do this in a dynamic for, I was thinking to do FOR THE FIRST PART...:
DECLARE @sql nvarchar(max);
DECLARE @Flag INT;
DECLARE @Size INT;
SET @Flag = 1
SET @Size = 10 ;
SET @sql = 'SELECT '+ CHAR(13);
WHILE (@Flag <= @Size) BEGIN
SET @sql = @sql + ' COALESCE([' + cast( @Flag as varchar(10)) + '], 0) ['+ cast( @Flag as varchar(10))+'],'+ CHAR(13) ;
SET @Flag = @Flag + 1;
END
SET @sql = LEFT(@sql, LEN(@sql) - 2)
SET @sql = @sql + '
FROM
(
SELECT x = a
, y = b
, v = val
FROM [dbo].[Matrix]
) AS dataToPivot
PIVOT
(
MIN(v)
FOR y IN (';
SET @Flag = 1
WHILE (@Flag <= @Size) BEGIN
SET @sql = @sql + ' [' + cast( @Flag as varchar(10)) + '],';
SET @Flag = @Flag + 1;
END
SET @sql = LEFT(@sql, LEN(@sql) - 1);
SET @sql = @sql +
')
) AS pivoted
ORDER BY x;'
print @sql
exec(@sql);
推荐答案
您可以使用COALESCE获得结果.
CREATE TABLE [dbo].[Matrix]
(
[A] INT
, [B] INT
, [VAL] FLOAT
);
INSERT INTO [dbo].[Matrix] ([A], [B], [VAL])
VALUES
(1, 1, -218.46)
, (1, 2, -2.1846000000000e+02)
, (2, 1, 6.4000000000000e+01)
, (2, 2, -218.46)
, (2, 3, 6.4000000000000e+01)
, (3, 2, -2.1846000000000e+02)
, (3, 3, -218.46)
, (4, 3, 6.4000000000000e+01)
, (4, 4, -218.46)
, (4, 5, 6.4000000000000e+01)
, (5, 4, 6.4000000000000e+01)
, (5, 5, -218.46)
, (5, 6, 6.4000000000000e+01)
, (6, 5, 6.4000000000000e+01)
, (6, 6, -218.46)
, (6, 7, 6.4000000000000e+01)
, (7, 6, 6.4000000000000e+01)
, (7, 7, -218.46)
, (7, 8, 6.4000000000000e+01)
, (8, 7, 6.4000000000000e+01)
, (8, 8, -218.46)
, (8, 9, 6.4000000000000e+01)
, (9, 8, 6.4000000000000e+01)
, (9, 9, -218.46)
, (9, 10, 6.4000000000000e+01)
, (10, 9, 6.4000000000000e+01)
, (10, 10, -218.46);
SELECT COALESCE([1], 0) [1]
, COALESCE([2], 0) [2]
, COALESCE([3], 0) [3]
, COALESCE([4], 0) [4]
, COALESCE([5], 0) [5]
, COALESCE([6], 0) [6]
, COALESCE([7], 0) [7]
, COALESCE([8], 0) [8]
, COALESCE([9], 0) [9]
, COALESCE([10], 0) [10]
FROM
(
SELECT x = a
, y = b
, v = val
FROM [dbo].[Matrix]
) AS dataToPivot
PIVOT
(
MIN(v)
FOR y IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])
) AS pivoted
ORDER BY x;
这篇关于如何将枢轴输出中的NULL值替换为零?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
