I have a table containing hundreds of columns many of which are null, and I would like have my select statement so that only those columns containing a value are returned. It would help me analyze data better. Something like:
我有一个包含数百列的表,其中许多列都是null,我想要我的select语句,以便只返回包含值的列。这将有助于我更好地分析数据。就像是:
Select (non null columns) from tablename;
从tablename中选择(非空列);
I want to select all columns which have at least one non-null value.
我想选择至少有一个非空值的所有列。
Can this be done?
可以这样做吗?
6 个解决方案
#1
3
Have a look as statistics information, it may be useful for you:
看看统计信息,它可能对您有用:
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.
SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP';
NUM_ROWS
----------
14
SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
2 where owner='SCOTT' and table_name='EMP' order by column_id;
COLUMN_NAME N NUM_DISTINCT NUM_NULLS
------------------------------ - ------------ ----------
EMPNO N 14 0
ENAME Y 14 0
JOB Y 5 0
MGR Y 6 1
HIREDATE Y 13 0
SAL Y 12 0
COMM Y 4 10
DEPTNO Y 3 0
8 rows selected.
For example you can check if NUM_NULLS = NUM_ROWS to identify "empty" columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.
例如,您可以检查NUM_NULLS = NUM_ROWS是否标识“空”列。参考:ALL_TAB_COLUMNS,ALL_TABLES。
#2
4
select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls=0;
Here is simple code to get non null columns..
这是获取非空列的简单代码。
#3
2
I don't think this can be done in a single query. You may need some plsql to first test what columns contain data and put together a statement based on that information. Of course, if the data in your table changes you have to recreate the statement.
我不认为这可以在一个查询中完成。您可能需要一些plsql来首先测试哪些列包含数据,并根据该信息汇总一个语句。当然,如果表中的数据发生更改,则必须重新创建语句。
declare
l_table varchar2(30) := 'YOUR_TABLE';
l_statement varchar2(32767);
l_test_statement varchar2(32767);
l_contains_value pls_integer;
-- select column_names from your table
cursor c is
select column_name
,nullable
from user_tab_columns
where table_name = l_table;
begin
l_statement := 'select ';
for r in c
loop
-- If column is not nullable it will always contain a value
if r.nullable = 'N'
then
-- add column to select list.
l_statement := l_statement || r.column_name || ',';
else
-- check if there is a row that has a value for this column
begin
l_test_statement := 'select 1 from dual where exists (select 1 from ' || l_table || ' where ' ||
r.column_name || ' is not null)';
dbms_output.put_line(l_test_statement);
execute immediate l_test_statement
into l_contains_value;
-- Yes, add column to select list
l_statement := l_statement || r.column_name || ',';
exception
when no_data_found then
null;
end;
end if;
end loop;
-- create a select statement
l_statement := substr(l_statement, 1, length(l_statement) - 1) || ' from ' || l_table;
end;
#4
2
Use the below:
使用以下:
SELECT *
FROM information_schema.columns
WHERE table_name = 'Table_Name' and is_nullable = 'NO'
Table_Name
has to be replaced accordingly...
必须相应地替换Table_Name ...
#5
0
select rtrim (xmlagg (xmlelement (e, column_name || ',')).extract ('//text()'), ',') col
from (select column_name
from user_tab_columns
where table_name='<table_name>' and low_value is not null)
#6
-1
What you're asking to do is establish a dependency on each row in the whole result. This is in fact not ever what you want. Just think of the ramifications if in one row every column had a value of '0' -- suddenly the schema of your result set grows to include all of those previously "empty" columns. You're effectively growing the badness of '*' exponentially, now your result set is not dependent on just the table's meta-data -- but your whole result set is dependent on the plain data.
你要做的是在整个结果中建立对每一行的依赖。事实上,这并不是你想要的。只要考虑一下,如果每一列的值都为'0',那么结果集的模式会突然增加,以包含所有以前“空”的列。你实际上是以指数方式增加了'*'的不良,现在你的结果集并不仅仅依赖于表的元数据 - 但是你的整个结果集依赖于普通数据。
What you want to do is just select the fields that have what you want, and not deviate from this simple plan.
你想要做的只是选择你想要的字段,而不是偏离这个简单的计划。
#1
3
Have a look as statistics information, it may be useful for you:
看看统计信息,它可能对您有用:
SQL> exec dbms_stats.gather_table_stats('SCOTT','EMP');
PL/SQL procedure successfully completed.
SQL> select num_rows from all_tables where owner='SCOTT' and table_name='EMP';
NUM_ROWS
----------
14
SQL> select column_name,nullable,num_distinct,num_nulls from all_tab_columns
2 where owner='SCOTT' and table_name='EMP' order by column_id;
COLUMN_NAME N NUM_DISTINCT NUM_NULLS
------------------------------ - ------------ ----------
EMPNO N 14 0
ENAME Y 14 0
JOB Y 5 0
MGR Y 6 1
HIREDATE Y 13 0
SAL Y 12 0
COMM Y 4 10
DEPTNO Y 3 0
8 rows selected.
For example you can check if NUM_NULLS = NUM_ROWS to identify "empty" columns.
Reference: ALL_TAB_COLUMNS, ALL_TABLES.
例如,您可以检查NUM_NULLS = NUM_ROWS是否标识“空”列。参考:ALL_TAB_COLUMNS,ALL_TABLES。
#2
4
select column_name
from user_tab_columns
where table_name='Table_name' and num_nulls=0;
Here is simple code to get non null columns..
这是获取非空列的简单代码。
#3
2
I don't think this can be done in a single query. You may need some plsql to first test what columns contain data and put together a statement based on that information. Of course, if the data in your table changes you have to recreate the statement.
我不认为这可以在一个查询中完成。您可能需要一些plsql来首先测试哪些列包含数据,并根据该信息汇总一个语句。当然,如果表中的数据发生更改,则必须重新创建语句。
declare
l_table varchar2(30) := 'YOUR_TABLE';
l_statement varchar2(32767);
l_test_statement varchar2(32767);
l_contains_value pls_integer;
-- select column_names from your table
cursor c is
select column_name
,nullable
from user_tab_columns
where table_name = l_table;
begin
l_statement := 'select ';
for r in c
loop
-- If column is not nullable it will always contain a value
if r.nullable = 'N'
then
-- add column to select list.
l_statement := l_statement || r.column_name || ',';
else
-- check if there is a row that has a value for this column
begin
l_test_statement := 'select 1 from dual where exists (select 1 from ' || l_table || ' where ' ||
r.column_name || ' is not null)';
dbms_output.put_line(l_test_statement);
execute immediate l_test_statement
into l_contains_value;
-- Yes, add column to select list
l_statement := l_statement || r.column_name || ',';
exception
when no_data_found then
null;
end;
end if;
end loop;
-- create a select statement
l_statement := substr(l_statement, 1, length(l_statement) - 1) || ' from ' || l_table;
end;
#4
2
Use the below:
使用以下:
SELECT *
FROM information_schema.columns
WHERE table_name = 'Table_Name' and is_nullable = 'NO'
Table_Name
has to be replaced accordingly...
必须相应地替换Table_Name ...
#5
0
select rtrim (xmlagg (xmlelement (e, column_name || ',')).extract ('//text()'), ',') col
from (select column_name
from user_tab_columns
where table_name='<table_name>' and low_value is not null)
#6
-1
What you're asking to do is establish a dependency on each row in the whole result. This is in fact not ever what you want. Just think of the ramifications if in one row every column had a value of '0' -- suddenly the schema of your result set grows to include all of those previously "empty" columns. You're effectively growing the badness of '*' exponentially, now your result set is not dependent on just the table's meta-data -- but your whole result set is dependent on the plain data.
你要做的是在整个结果中建立对每一行的依赖。事实上,这并不是你想要的。只要考虑一下,如果每一列的值都为'0',那么结果集的模式会突然增加,以包含所有以前“空”的列。你实际上是以指数方式增加了'*'的不良,现在你的结果集并不仅仅依赖于表的元数据 - 但是你的整个结果集依赖于普通数据。
What you want to do is just select the fields that have what you want, and not deviate from this simple plan.
你想要做的只是选择你想要的字段,而不是偏离这个简单的计划。