September 16, 2011 at 6:22 am
I have Table with Data as
Create TAble Test(Id int, name varchar(50))
GO
insert into Test values(1,'Test1')
GO
insert into Test values(2,'Test2')
GO
select * from Test
--I have another table variable where Name of columns of Table Test are stored
Declare @tbl as Table
(Id int identity(1,1),
ColName varchar(5));
insert into @tbl
values ('ID')
insert into @tbl
values ('name')
select * from @tbl
--I want Result set as
declare @ColName varchar(50)
select @ColName = colname from @tbl where ID=1
select @ColName from Test
It should display the value of Column not the name of Column
i dont want dynamic sql like exec(@sql)
Any other Solution
Thanks in advance
September 16, 2011 at 6:54 am
You do realise that "adding proper column names" is typical front-end task, don't you. i.e. you shouldn't want to do that in T-SQL.
There is only one way you can 'change' the name of the columns in your result set: by adding the 'as' keyword after the column name in your select clause. So, you'll have to either hard code the query like:
select tbl.col1 as [ID], tbl.col2 as [Name]
from dbo.MyTable tbl
Or you'll have to construct the above statement and then execute it using sp_executesql (or exec). There is no other way.
September 16, 2011 at 8:05 am
Not to mention how would you do that when there is more than 1 row? Which row of data is the correct row to use as the column name? The business logic for this seems to be a complicated way to do whatever it is you are trying to do.
There are a few ways you can alias column names.
As RP said
select column as MyName from MyTable
The "as" keyword is optional but I find this way particularly bad because it can lead to mistakes quite easily and it is harder to read imho.
select column MyName from MyTable
You can also name the column first like. This way is confusing for me because when I am scanning code I tend to notice the = and think it is a variable being set and not a column being aliased.
select MyName = column from MyTable
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply