omitting null columns

Hey everyone, I haven't got any idea where to even start looking for a solution to this problem so any help would be appreciated. I'm trying to write a stored procedure that will return a table yet omit any columns that don't have anything written to them, so any columns that have no data will not be present in the table returned after the stored procedure.

Thanks in advance.

Hi Mitch,

Here you could take advantage of "IS NOT NULL" for example;

SELECT
*
FROM
MyTable
WHERE
ColumnName IS NOT NULL

|||Is there a way to do it more like

SELECT
*
FROM
MyTable
WHERE
AnyColumn IS NOT NULL

certain tables have different column names so I need the code to be flexible enough to omit any column it finds that is null in any table I give it.
|||Wait sorry that doesn't work, that tell it to select any rows that contain anything that is not null in 'x' column. I need it to select that row still but omit 'x' column so.

| A | B | C |
| 1 | | 2 |

becomes

| A | C |

| 1 | 2 ||||

just concatenate the columns, this assumes that the SET CONCAT_NULL_YIELDS_NULL is ON (which is the default)

Example
CREATE TABLE TestNulls (c1 INT,c2 INT, c3 INT,c4 INT,c5 INT, c6 INT)
INSERT TestNulls VALUES (1,1,1,1,1,1)
INSERT TestNulls VALUES (2,1,1,1,1,1)
INSERT TestNulls VALUES (3,1,1,1,1,1)
INSERT TestNulls VALUES (4,NULL,1,1,1,1)
INSERT TestNulls VALUES (5,1,1,1,1,1)
INSERT TestNulls VALUES (6,1,1,1,1,1)
INSERT TestNulls VALUES (7,1,1,NULL,1,1)

SELECT *
FROM TestNulls
WHERE C1+C2+C3+C4+C5+C6 IS NOT NULL

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

Never mind the answer I gave you before, I didn't see this requirement

You should handle stuff like this client side not server side it is just adding unnecessary processing on the DB side

To do this on the DB you will have to use <evil>dynamic SQL</evil> dump the result in a temp table and build the SELECT statement dynamically by using a bunch of EXISTS...not pretty at all

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Menace is right: The dynamic SQL to accomplish what you want is pretty ugly. Here is an example of one way to do it:

create table dbo.example
( column_01 integer,
column_02 float,
column_03 integer,
column_04 varchar(30)
)
go

insert into example values (1, null, 2, 'This is a test.')
insert into example values (2, null, null, null)
insert into example values (3, null, 3, 'Tell me.')
go

declare @.execString varchar (6000)
declare @.columnString varchar (5000) set @.columnString = ''

select @.columnString = @.columnString + x.columnName + ','
from ( select q.colid,
cast (q.columnName as varchar(20)) as columnName
from ( select max ( case when column_01 is null then 0
else 1 end ) as column_01,
max ( case when column_02 is null then 0
else 2 end ) as column_02,
max ( case when column_03 is null then 0
else 3 end ) as column_03,
max ( case when column_04 is null then 0
else 4 end ) as column_04
from example
) p
unpivot
( colid for columnName in
( [column_01], [column_02], [column_03], [column_04] )
) q
group by q.colid, q.columnName
having q.colid <> 0
) x

select @.execString
= 'select '
+ left (@.columnString, len(@.columnString)-1)
+ ' from example '

exec ( @.execString )

-- -- Output: --

-- column_01 column_03 column_04
-- -- --
-- 1 2 This is a test.
-- 2 NULL NULL
-- 3 3 Tell me.

|||

Menace is right: The dynamic SQL to accomplish what you want is pretty ugly. Here is an example of one way to do it:

create table dbo.example
( column_01 integer,
column_02 float,
column_03 integer,
column_04 varchar(30)
)
go

insert into example values (1, null, 2, 'This is a test.')
insert into example values (2, null, null, null)
insert into example values (3, null, 3, 'Tell me.')
go

declare @.execString varchar (6000)
declare @.columnString varchar (5000) set @.columnString = ''

select @.columnString = @.columnString + x.columnName + ','
from ( select q.colid,
cast (q.columnName as varchar(20)) as columnName
from ( select max ( case when column_01 is null then 0
else 1 end ) as column_01,
max ( case when column_02 is null then 0
else 2 end ) as column_02,
max ( case when column_03 is null then 0
else 3 end ) as column_03,
max ( case when column_04 is null then 0
else 4 end ) as column_04
from example
) p
unpivot
( colid for columnName in
( [column_01], [column_02], [column_03], [column_04] )
) q
group by q.colid, q.columnName
having q.colid <> 0
) x

select @.execString
= 'select '
+ left (@.columnString, len(@.columnString)-1)
+ ' from example '

exec ( @.execString )

-- -- Output: --

-- column_01 column_03 column_04
-- -- --
-- 1 2 This is a test.
-- 2 NULL NULL
-- 3 3 Tell me.

|||

use below if you have the list of columns otherwise.. you'll have to query the syscolumn table.

select *
from mytable
where column1 is not null
and column2 is not null
and column3 ...

Mitch Wardrop wrote:

Is there a way to do it more like

SELECT
*
FROM
MyTable
WHERE
AnyColumn IS NOT NULL

certain tables have different column names so I need the code to be flexible enough to omit any column it finds that is null in any table I give it.