如何只选择Sql Server中不为空的列。？ [英] How Can I Select Only The Columns Which Is Not Null In Sql Server.?

问题描述

我有一个名为test的表，其中包含app1，app2，app3，app4，app5 .... appn。

我想要的列只选择那些值不为空的列。

如果我这样做如下。



select * from test where id = 10



结果会像。

app1 app2 app3 app4 app5 ................. ..... appn

3000 2000 5000 null null ....................... null



i想要选择如下。

app1 app2 app3

3000 2000 5000



注意： - 表测试有60多列和多行。



所以如何查询。？

select * from测试其中id = 10和__________

解决方案

据我所知，你不能用 SQL （和如评论中所述，我会重新考虑显然存在缺陷的数据库设计。

但是，您可以使用编程语言来丢弃检索d null values。

你可以'这样做'，但这是一个非常低效的查询。我建议查看db重新设计或管理null列的返回集。



基本上，你必须使用pivot将列转换为行，选择IS NOT NULL然后再将它们重新打开。



就像我说的那样，效率并不高。该功能仅在T SQL中按要求提供，而不是设计。



不仅因为这个原因它不理想，它也是一个让你头脑发热的人。这是一个帮助您入门的链接：

https://technet.microsoft.com/en-us/library/ms177410(v = sql.105）.aspx [ ^ ]



我<我强烈建议你不要使用这种技术，并找到与其他解决方案中提出的不同的解决方案。按照这条路线，您同意不会发现我对可能导致的任何偏头痛或脑动脉瘤负责



祝你好运^ _ ^

< blockquote>

 声明  @ query   varchar （max）
 声明  @ querybit   varchar （max）
 
 选择  @ querybit  =（ SELECT   TOP   1 （< span class =code-keyword>选择 SUBSTRING（（选择 '  +' + name 来自 sys.all_columns 
 其中 object_id =  OBJ ECT_ID （'  yourtablename'）
 和（system_type_id =  167 ）
   XML PATH（' '））， 2 ， 20000 ））来自 yourtablename）
 
 选择  @ query  = '  SELECT' +  @ querybit  + '  FROM yourtablename' 
 
  exec （ @ query ）

可以做这样的事......你需要检查sys.types是否有正确的system_type_id s和那希望你的列都是相同的数据类型......



和oops ...读错的问题我正在寻找过滤行的方法在某些列中为空...

Hi,
I have a table called "test" which has columns like app1,app2,app3,app4,app5.... appn.
I want to select only those columns which value not null.
like if i do as below.

select * from test where id = 10

result will come like.
app1 app2 app3 app4 app5 ......................appn
3000 2000 5000 null null.......................null

i want to select as below.
app1 app2 app3
3000 2000 5000

NOTE:- Table test has 60+ columns and many rows.

so how will be query.?
select * from test where id = 10 and __________

解决方案

As far as I know you cannot do that with SQL (and as stated in the comment, I would re-think a database design that is apparently flawed).
You could, however, use a programming language for discarding retrieved null values.

You 'can' do this, but it's a VERY inefficient query. I would suggest looking at a db redesign or managing the return set for null columns.

Basically, you have to use pivot to turn columns into rows, select IS NOT NULL and then unpivot them back again.

Like I said, this is not at all efficient. The feature is only in T SQL by request, not design.

Not only is it not ideal for that reason, it's also a bugger to get your head around. Here is a link to help you get started:
https://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx[^]

I strongly suggest you don't use this technique and find a different solution to your problem as has been suggested in other solutions. By following this route you agree to not find me responsible for any migraines or brain aneurysm that may result

Good luck ^_^

declare @query varchar(max)
declare @querybit varchar(max)

select @querybit = (SELECT TOP 1(select SUBSTRING((select '+ ' + name from sys.all_columns
where object_id = OBJECT_ID('yourtablename')
and (system_type_id = 167)
for XML PATH('')),2,20000)) from yourtablename)

select @query = 'SELECT' + @querybit + ' FROM yourtablename'

exec (@query)

could do something a bit like this... you'll need to check sys.types for the correct system_type_id 's and thats hoping that your columns are all the same datatype...

and oops... read the question wrong I was looking at ways of filtering out rows that had null in some columns...

这篇关于如何只选择Sql Server中不为空的列。？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！