select all columns that are not null - dave ballantyne

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

select all columns that are not null - Rick Sawtell

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

select all columns that are not null - Uri Dimant

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'

select all columns that are not null - dave ballantyne

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

select all columns that are not null - Billy

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.

select all columns that are not null - Billy

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=A0am, dave ballantyne <syml...@nospammeever.yahoo.com>

select all columns that are not null - billy.roger

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

select all columns that are not null - Alex Kuznetsov

This indicates a poor database design. Can you describe your problem
so that we can design a better table for your needs?

select all columns that are not null - billy.roger

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.

select all columns that are not null - dave ballantyne

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