April 24, 2002 at 8:54 am
Hi Guys
Does any one know if it is possible to generate Column headers in select statements out of variable values without using dynamic sql?
ie:
declare @header varchar(50)
set @header = 'TestColumn'
select 1 as @header
so that it returns:
TestColumn
----------
1
It can be done through dynamic, eg:
set @execStmt =
'select 1 as ' + cast(@header as varchar(10))
exec(@execStmt)
Just wondered if anyone knew if this was possible without using dynamic sql.
Cheers
Davidt
April 24, 2002 at 9:30 am
I don't think it possible.
You can do it shorter:
declare @header varchar(50)
set @header = 'TestColumn'
exec ('select 1 as ' + @header)
April 24, 2002 at 10:05 am
Thanks
I had never heard of it, but it seemed like something that might have been possible, as you would have thought that having a variable column header name would not have effected a compiled execution plan.
April 26, 2002 at 2:12 am
A bit of a bodge, I know, but you could do :-
declare @header varchar(50)
set @header = 'TestColumn'
select @header
union
select convert(varchar(10),1)
order by 1 desc
It would mean that you'd have to do converts on every column to varchar's and you have to extract the first row as the header row. - which is OK if you're returning it as a recordset.
April 26, 2002 at 7:34 am
Cool, never thought of it!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply