Can i select the column name returned in a query?

[stephenk1973]

Can i select the column name returned in a query?

select 10 As (select getdate()),103)), 'apples' as Veg from...

Would like to return

15/11/2005 Veg
10 apples

Thanks

Stephen

 

[ChrisDodgson]

What is it you're trying to do exactly?

Chris Dodgson

 

[swampBoogie]

declare @s varchar(200)
set @s = 'select 10 as '''
+ convert(varchar(10),getdate(),103) +
 ''' ,''apples'' as veg from ... '
 exec(@s)

 

[stephenk1973]

Sorry if there is some confusion in my question, I tried to simplify to be more direct of what i want to do. I am aiming towards a cross-tab query -view.Columns will relate to consecutive Saturdays, so I would like my result tol look like…

Veg 19/11/2005 26/11/2005 03/12/2005…….
Apples 12 13 14
Potatoes 5 6 5

Currently I return colmns as Week1, Week2, Week3 etc, was thinking I could make these more meaningful putting the date the week relates to as the column head. I can do this by creating a store procedure to update the view, sort of down swapboogie’s path of generating the string, but would ideally like to have one query set, no maintenance, hence the dynamic column naming.

Hope this explains what I’m trying to do.

Stephen

 

[ChrisDodgson]

I believe that the only way that a crosstab approach works in SQL is if you specify the actual column names and the logic of what you want them to contain... so that being the case you would need something that looks a little like:

select Veg,
sum(case [relevantdate] when '2005-11-19' then 1 else 0 end) as [19/11/2005]
from tbl_vegetable_related_stuff
group by Veg

Really what you're trying to do is something that would work better either by using Analysis Services, or maybe even automate an export out to Excel, and then use some VBE to create a pivot table.

Hope this helps,

Chris

 

[ChrisDodgson]

Having thought about it some more for about 20 seconds, if the report has a fixed number of columns you could create a
table as follows:

create table veg(colA varchar (50), colB varchar (50) etc...)

then insert into the table:

insert into table Veg
select 'Veg', dateadd(dd, -14, getdate()), dateadd(dd, -7, getdate()), getdate()

then insert into that the table the actual result set which (in my warped brain) would look like :

Apples 19 4 36
Orange 1 20 5


Then finish off with a straightforward 'select * from Veg'.

Of course, this process will only work if it's run a specific time...

(does this help... or am I just a little bit crazy...)

Thanks,

Chris

 

[druer]

Stephen, I think the example from swampBoogie gives you the idea of how to use DynamicSQL to create the column names you are looking for.

 

[stephenk1973]

Had thought along those lines myself, but thought what i described might be tidier, can see what you mean though and how it could work.

You have got me thinking , i was a bit tracked into thinking my problem would have a direct solution. I think my best solution will be to return week1,week2, etc and then alter the label in the load of the presntation layer. On export to excel can put a 'replace' into the code to name the columns correctly

By the way i do know apples and oranges aren't vegtables ;-)

All the best, thanks again.

Stephen