CODEWITHSUNDEEP
sqlsql-server
Valentin C
Valentin C

Reputation: 161

Where result in some columns are null

I'm trying to reduce the amount of rows I insert in a table every day.

In about 5 to 10% of them, All I got is Primary Keys Values. I'm wondering if there was any method to select only rows that got more than PK values.

For exemple, the result I got now :

PK1    PK2    cl1    cl2    cl3    cl4
---------------------------------------------------------------------------
a      Louis  null   null   171    15
a      Jack   13     null   null   null
a      Marty  17     14     130    null
b      Louis  10     11     110    12
b      Jack   03     null   185    null
b      Marty  null   null   null   null
c      Louis  16     48     null   25
c      Jack   null   null   null   null
c      Marty  13     null   null   64
d      Louis  null   null   null   null
d      Jack   21     12     165    null
d      Marty  null   null   null   null

My desired result :

PK1    PK2    cl1    cl2    cl3    cl4
---------------------------------------------------------------------------
a      Louis  null   null   171    15
a      Jack   13     null   null   null
a      Marty  17     14     130    null
b      Louis  10     11     110    12
b      Jack   03     null   185    null

c      Louis  16     48     null   25

c      Marty  13     null   null   64

d      Jack   21     12     165    null

Is there any request possible in SQL that can change to that ? For now I'm using this one below

SELECT 
    table.PK1,table.PK2,
    table.cl1,table.cl2,table.cl3,table.cl4,
FROM
    datatable table

Thanks in advance

EDIT : this is an exemple, there is more than a hundred column (not pk) and I don't want to filter with "where". (or just in worst case)

Upvotes: 3

Views: 204

Answers (3)

DxTx
DxTx

Reputation: 3357

If you don't want to write columns names, you can do something like this.

DECLARE @query NVARCHAR(MAX);

SELECT @query = ISNULL(@query+', ','') + [name] 
                FROM  sys.columns 
                WHERE object_id = OBJECT_ID('yourTableName') 
                AND  [name] != 'PK1' AND  [name] !=  'PK2';

SET @query = N'SELECT * FROM TmpTable WHERE COALESCE('+ @query +') IS NOT NULL';

EXECUTE(@query)

A row will be ignored when all of the columns (cl1, cl2, cl3, cl4, ..., cln = null) values are null, except for the first two columns or for the columns you specified.(PK1, PK2)

Output

Output Result

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

The simplest way is just to explicitly remove the rows where all are NULL:

where cl1 is not null or cl2 is not null or cl3 is not null . . .

There is not really a short-cut. Although you can use coalesce(), that prevents the use of indexes on the query (but they weren't used anyway).

If you really want performance for this query, you can add a computed column:

alter table t add num_nulls as ( (case when col1 is null then 1 else 0 end) + (case when col2 is null then 1 else 0 end) + . . . );

Then you can add an index on t(num_nulls) and use this in a where clause. For filtering out 5-10% of the rows, this is not effective. But if you wanted to filter out 98%, then it would be more performant.

Upvotes: 1

Andomar
Andomar

Reputation: 238086

You can use the coalesce function. It takes any number of arguments and returns the first non-null argument. So for example:

select coalesce(3, 1, 4);            -->  3
select coalesce(3, null, 4);         -->  3
select coalesce(null, 1, 4);         -->  1
select coalesce(null, null, 4);      -->  4
select coalesce(null, null, null);   -->  null

You can use this to check if all columns are null:

where coalesce(col1, col2, ..., colN) is null

Upvotes: 3

Related Questions