如果任何行值为null，如何为pivot查询排除null值 [英] how to exclude null value for pivot query if any of the row value is null

问题描述

date	18 	20	50	909 --headers
2015-03-30 	20	153	92	41
2015-03-31 	20	153	93	41
2015-02-19 	571	160	92  NULL
2015-04-06 	52	155	95	42
2015-04-07 	80	155	95	42

out put应该是这样的

out put should be like this

date	18 	20	50	909 --headers
2015-03-30 	20	153	92	41
2015-03-31 	20	153	93	41
2015-02-19 	571	160	92  NULL
2015-04-06 	52	155	95	42
2015-04-07 	80	155	95	42

推荐答案

您好，



据我所知，你在数据透视表中做了一个select *。

所以，



您可以选择...选择* [您的数据透视表] t [t.requiredcolumn]不为空。



类似，

Hi,

As i understand you are doing a select * in the pivot table.
so,

Can you select like .. select * [your pivot table] t where [t.requiredcolumn] is not null.

something like,

select * from
 ( dataTable1
pivot 
datatable2)

 as pivotTable where reqcol is not null

order by 2

希望这会有所帮助。





快乐编码！

Hope this helps.


Happy coding!

源查询应如下所示：

A source query should looks like this:

select s.code,p.date,p.rs from Price as p
inner join SCH as s
on s.CODE=p.CODE
where s.CODE in (18,20,50,909) AND NOT p.rs IS NULL
and p.date BETWEEN ' +' '''+@fromdate+''' '+ ' and '+' '''+@todate+''' '+ '

如果sourcce表不包含 N ULL 值，您必须过滤数据透视表：

If sourcce table does not contain NULL values, you have to filter pivot table:

SELECT pt.<Fields>
FROM (
    -- source query
) AS st
PIVOT (...) AS pt
WHERE NOT pt.[909] IS NULL

另一种方法是使用 COALESCE函数将NULL替换为默认值 [ ^ ]：

Another way is to replace NULLs with default values via using COALESCE function[^]:

SELECT COALESCE(pt.[18], 0) AS [18] -- and so on

这篇关于如果任何行值为null，如何为pivot查询排除null值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！