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
根据上一个问题的答案，我想在某些情况下没有数字时除去一些NULL值
I have the following query
我有以下查询
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
I am getting the following result:
我得到以下结果：
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
but I need the following result
但我需要以下结果
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
How do I replace all NULL values with 0's?
如何用0替换所有NULL值？
EDIT
Following @Siva answer
关注@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);
3 个解决方案
#1
2
You can use COALESCE to achieve the result.
您可以使用COALESCE来实现结果。
SQL小提琴
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;
#2
2
How about IsNull?
IsNull怎么样？
SELECT
isnull([1], 0) as [1],
isnull([2], 0) as [2],
isnull([3], 0) as [3],
isnull([4], 0) as [4],
isnull([5], 0) as [5],
isnull([6], 0) as [6],
isnull([7], 0) as [7],
isnull([8], 0) as [8],
isnull([9], 0) as [9],
isnull([10], 0) as [10]
FROM
(SELECT x=a, y=b, v= CASE WHEN IsNumeric(val)=0 THEN 0 ELSE IsNull(val, 0) 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
#3
1
I used the dynamic pivot technique in this SO question Simple way to transpose columns and rows in Sql?
我在这个SO问题中使用了动态数据透视技术在Sql中转换列和行的简单方法？
To put NULLs in my pivot report like below. Does get more messy though.
将NULL放在我的数据透视报表中，如下所示。虽然变得更加凌乱。
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);
DECLARE @Sql nvarchar(max), @PvtCols nvarchar(max), @PvtSelect nvarchar(max)
SELECT @PvtSelect = Stuff(
(
SELECT ',IsNull(' + Quotename(B) + ',0) as ' + Quotename(B)
FROM MYTABLE mt
GROUP BY B
ORDER BY B
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SELECT @PvtCols = Stuff(
(
SELECT DISTINCT ',' + Quotename(B)
FROM MYTABLE mt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Sql =
'SELECT ' + @PvtSelect + '
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 (' + @PvtCols + ')
) AS pvt
order by x'
EXEC(@SQL)
#1
2
You can use COALESCE to achieve the result.
您可以使用COALESCE来实现结果。
SQL小提琴
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;
#2
2
How about IsNull?
IsNull怎么样？
SELECT
isnull([1], 0) as [1],
isnull([2], 0) as [2],
isnull([3], 0) as [3],
isnull([4], 0) as [4],
isnull([5], 0) as [5],
isnull([6], 0) as [6],
isnull([7], 0) as [7],
isnull([8], 0) as [8],
isnull([9], 0) as [9],
isnull([10], 0) as [10]
FROM
(SELECT x=a, y=b, v= CASE WHEN IsNumeric(val)=0 THEN 0 ELSE IsNull(val, 0) 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
#3
1
I used the dynamic pivot technique in this SO question Simple way to transpose columns and rows in Sql?
我在这个SO问题中使用了动态数据透视技术在Sql中转换列和行的简单方法？
To put NULLs in my pivot report like below. Does get more messy though.
将NULL放在我的数据透视报表中，如下所示。虽然变得更加凌乱。
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);
DECLARE @Sql nvarchar(max), @PvtCols nvarchar(max), @PvtSelect nvarchar(max)
SELECT @PvtSelect = Stuff(
(
SELECT ',IsNull(' + Quotename(B) + ',0) as ' + Quotename(B)
FROM MYTABLE mt
GROUP BY B
ORDER BY B
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SELECT @PvtCols = Stuff(
(
SELECT DISTINCT ',' + Quotename(B)
FROM MYTABLE mt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
SET @Sql =
'SELECT ' + @PvtSelect + '
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 (' + @PvtCols + ')
) AS pvt
order by x'
EXEC(@SQL)