select all columns that are not null

"Billy" wrote in message

> Is there a way to add a where clause to this statement
>
> SELECT * FROM tableName
>
> so that you only return columns that don't have a null in each row?
> I don't want blank columns returned......but this will change each
> month so I don't want to hard code the column names.

You would need to do that with dynamic SQL.

DECLARE @sql varchar(8000)

SELECT @sql = 'SELECT ' + ISNULL(col1, col1_name) + ISNULL(col2, ', ' +
col2_name) + ISNULL(col3, ', ' + col3_name) + ' FROM tablename'

-- You should probably add some code to ensure that you have at least one
column in the column list.


EXECUTE (@sql)



Rick Sawtell
 >> Stay informed about: select all columns that are not null 

I don't quite understand how to use this. Where do I put the table
name?

Here's the table

CREATE TABLE [dbo].[MRecordCount] (
[Platform] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[FDMS] [bit] NOT NULL ,
[_20070301] [int] NULL ,
[_20070401] [int] NULL ,
[_20070501] [int] NULL ,
[_20070601] [int] NULL ,
[_20070701] [int] NULL ,
[_20070801] [int] NULL ,
[_20070901] [int] NULL ,
[_20071001] [int] NULL ,
[_20071101] [int] NULL ,
[_20071201] [int] NULL ,
[_20080101] [int] NULL ,
[_20080201] [int] NULL ,
[_20080301] [int] NULL ,
[_20080401] [int] NULL ,
[_20080501] [int] NULL ,
[_20080601] [int] NULL ,
[_20080701] [int] NULL ,
[_20080801] [int] NULL ,
[_20080901] [int] NULL
) ON [PRIMARY]
GO
 >> Stay informed about: select all columns that are not null 

On May 14, 7:46 am, Billy wrote:
>.but this will change each
> month so I don't want to hard code the column names.

This indicates a poor database design. Can you describe your problem
so that we can design a better table for your needs?
 >> Stay informed about: select all columns that are not null 

I know it's a poor design. I know the data should be going down and
not accross. I didn't design or populate this table.




On May 14, 1:28 pm, Alex Kuznetsov wrote:
> On May 14, 7:46 am, Billy wrote:
>
> >.but this will change each
> > month so I don't want to hard code the column names.
>
> This indicates a poor database design. Can you describe your problem
> so that we can design a better table for your needs?
 >> Stay informed about: select all columns that are not null 

Billy wrote:
> Is there a way to add a where clause to this statement
>
> SELECT * FROM tableName
>
> so that you only return columns that don't have a null in each row?
> I don't want blank columns returned......but this will change each
> month so I don't want to hard code the column names.



Sounds as if you have made a fundemental error in your understanding of
relational databases.

It sounds as if you table is


PKey Int,
Month1 int,
Month2 int,
Month3 int,
Month4 int,
.....
....
...


It Should be

PKey Int
MonthNum Int
MonthValue int


And then populated with rows not columns.

Dave
 >> Stay informed about: select all columns that are not null 

Billy wrote:
> Yes I am working with data that is not normalized. That doesn't mean
> I don't realize it. I have to work with the data the way it is.
>
> On May 14, 8:00 am, dave ballantyne
> wrote:
>> Billy wrote:
>>> Is there a way to add a where clause to this statement
>>> SELECT * FROM tableName
>>> so that you only return columns that don't have a null in each row?
>>> I don't want blank columns returned......but this will change each
>>> month so I don't want to hard code the column names.
>> Sounds as if you have made a fundemental error in your understanding of
>> relational databases.
>>
>> It sounds as if you table is
>>
>> PKey Int,
>> Month1 int,
>> Month2 int,
>> Month3 int,
>> Month4 int,
>> ....
>> ...
>> ..
>>
>> It Should be
>>
>> PKey Int
>> MonthNum Int
>> MonthValue int
>>
>> And then populated with rows not columns.
>>
>> Dave
>



If i understand you right, this should help. Ive done it as a two pass
operation to avoid multiple scans on the source table, that may or may
not bother you.

Dave


Create Table xxtestt1
(
col1 int null,
col2 int null,
col3 int null,
col4 int null)

insert into xxtestt1 values(Null,1,Null,NULL)
go


drop table xxskeltab
go

declare @colname varchar(20)
declare @SqlString nvarchar(2048)


declare tcursor cursor for
select name from syscolumns
where id = object_id('xxtestt1')
open tcursor
while(0=0) begin
fetch next from tcursor into @Colname

if(@@Fetch_status<>0) break

if @SqlString is null begin
Select @SqlString = 'SELECT '
end else begin
Select @SqlString = @SqlString+','
end
Select @SqlString = @SqlString +@ColName+'= max(case when
'+@Colname+' is not null then 1 else 0 end) '
end
close tcursor
deallocate tcursor

Select @SqlString = @SqlString +' into xxskeltab From xxtestt1'
select @SqlString
EXECUTE sp_executesql @SQLString;
go

Declare @SqlString nvarchar(2048)
declare @FullSql nvarchar(2048)
declare @Colname varchar(20)
declare @Colret integer

declare tcursor cursor for
select name from syscolumns
where id = object_id('xxskeltab')
open tcursor
while(0=0) begin
fetch next from tcursor into @Colname

if(@@Fetch_Status<>0) break
Select @SqlString = 'Select @ColRetout='+@Colname+' from xxskeltab '
exec sp_executesql @SqlString,N'@ColRetout integer
Output',@ColRetout =@ColRet OUTPUT

if(@ColRet=1) begin
if(@Fullsql is null) begin
Select @FullSql = 'Select '
end else begin
Select @FullSql = ','
end
Select @FullSql = @FullSql + @Colname
end
end
close tcursor
deallocate tcursor

select @FullSql = @Fullsql +' from foo'
select @FullSql
 >> Stay informed about: select all columns that are not null 

Billy

not tested

Open the cursor, run

SET @SQL = N'IF EXISTS (SELECT '+ @column +' FROM'') SET
@ALLNULL = ''N'' ELSE set @ALLNULL = ''Y'''
SET @ParamDefinition = N'@column sysname, @ALLNULL CHAR(1) OUTPUT'

EXECUTE sp_executesql
@SQL ,
@ParamDefinition,
@column= @column,
@ALLNULL = @ALLNULL OUTPUT

IF @ALLNULL = 'Y' PRINT @column+ ' has all rows NULL'


"Billy" wrote in message

> Is there a way to add a where clause to this statement
>
> SELECT * FROM tableName
>
> so that you only return columns that don't have a null in each row?
> I don't want blank columns returned......but this will change each
> month so I don't want to hard code the column names.
 >> Stay informed about: select all columns that are not null 

billy.rogers.RemoveThis@chasepaymentech.com wrote:
> I don't quite understand how to use this. Where do I put the table
> name?
>
> Here's the table
>
> CREATE TABLE [dbo].[MRecordCount] (
> [Platform] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TableName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [FDMS] [bit] NOT NULL ,
> [_20070301] [int] NULL ,
> [_20070401] [int] NULL ,
> [_20070501] [int] NULL ,
> [_20070601] [int] NULL ,
> [_20070701] [int] NULL ,
> [_20070801] [int] NULL ,
> [_20070901] [int] NULL ,
> [_20071001] [int] NULL ,
> [_20071101] [int] NULL ,
> [_20071201] [int] NULL ,
> [_20080101] [int] NULL ,
> [_20080201] [int] NULL ,
> [_20080301] [int] NULL ,
> [_20080401] [int] NULL ,
> [_20080501] [int] NULL ,
> [_20080601] [int] NULL ,
> [_20080701] [int] NULL ,
> [_20080801] [int] NULL ,
> [_20080901] [int] NULL
> ) ON [PRIMARY]
> GO

Hi Billy,

replace xxtestt1 and foo in the final select statement that is built
with MRecordCount.


Put just to restate , this is a truly horrible and wrong table design,
adding columns rather than rows is not good practice.

Dave
 >> Stay informed about: select all columns that are not null