Variables as labels 1

[DBAssam]

I can't seem to get the following to work on a very simple
names and address database with

Name, Addr1, Addr2, Addr3, Town, County etc

What I want to do is select one single record and show only the fields that are not null and call the text labels 1,2,3,4 etc

Therefore:

Name Addr1 Addr2 Addr3 Town
--------------------------------------------
J Smith 12 Smith Drive Null Null Smithville


Would Read:

1 2 3
----------------------------------------------
J Smith 12 Smith Drive Smithville

Can't seem to make variables appear as fieldnames and
the case statements don't like it either.

Any ideas anyone? Gratefully received.

thanks

D

 

[SQLBill]

How about using alias':

SELECT [name] as 1, addr1 as 2, addr2 as 3..........etc.


-SQLBill

 

[anilcognos]

try this

Select name as '1', Addr1 as '2',addr2 as '3'
from Table

 

[LFCfan]

this looks ridiculous, but seems to be doing what i think you want it to do.

Gave me something to do in my lunch break anyway - the weather's too dreadful to go out in!

Hope it helps
Maria

****************************
create table #tmp (fname varchar(40), addr1 varchar(40), addr2 varchar(40), addr3 varchar(40), town varchar(40))
insert into #tmp values('J Smith','12 Smith Drive',null,null,'Smithtown')

declare @add_column int
set @add_column = 0
declare @column varchar(40)
declare @strSQL varchar(300)
declare @search_arg varchar(40)

select @column = fname from #tmp where fname = 'J Smith'
set @add_column = 1
set @strSQL = 'select ''' + @column + '''as '+ '''' + cast(@add_column as varchar(4)) + ''''
select @column
print @strSQL

select @column = addr1 from #tmp where fname = 'J Smith'
set @add_column = case when @column is null then @add_column else @add_column + 1 end
set @strSQL = case when @column is null then @strSQL else @strSQL + ', ''' + @column + '''' + ' as ''' + cast(@add_column as varchar(4)) + '''' end
print @strSQL

select @column = addr2 from #tmp where fname = 'J Smith'
set @add_column = case when @column is null then @add_column else @add_column + 1 end
set @strSQL = case when @column is null then @strSQL else @strSQL + ', ''' + @column + '''' + ' as ''' + cast(@add_column as varchar(4)) + '''' end
print @strSQL

select @column = addr3 from #tmp where fname = 'J Smith'
set @add_column = case when @column is null then @add_column else @add_column + 1 end
set @strSQL = case when @column is null then @strSQL else @strSQL + ', ''' + @column + '''' + ' as ''' + cast(@add_column as varchar(4)) + '''' end
print @strSQL

select @column = town from #tmp where fname = 'J Smith'
set @add_column = case when @column is null then @add_column else @add_column + 1 end
set @strSQL = case when @column is null then @strSQL else @strSQL + ', ''' + @column + '''' + ' as ''' + cast(@add_column as varchar(4)) + '''' end
print @strSQL

exec(@strSQL)

drop table #tmp

 

[DBAssam]

Maria's code did exactly what I wanted. Genuinely thanks for that Maria.

David