Consider the below table "sample_table"
id name desc
-------------------------
1 kkt kkt description
1 skt skt description
is there anyway I can select the third column without using its name desc?
Trying select desc from mytable throws an error
Please suggest.
Thanks,
Balan
I fail to see why you need this and I would never use it myself.
declare @T table
(
id int,
name varchar(10),
description varchar(25)
)
insert into @T values
(1, 'kkt', 'kkt description'),
(1, 'skt', 'skt description')
select T2.N.value('*[3]', 'varchar(max)')
from (select *
from @T
for xml path('r'), type) as T1(X)
cross apply T1.X.nodes('/r') as T2(N)
Update
You should do like this instead.
select [desc]
from YourTable
Use [] around column names that is reserved words.
Use standard SQL quoted identifiers around column names that are reserved words e.g.
SELECT "desc" FROM sample_table;
select mytable.desc from mytable;
work fine for me
Does this help:
Declare @WhichOne int;
Declare @Sql varchar(200);
SET @WhichOne = 2;
WITH cte AS
(SELECT name, Row_Number() Over (ORDER BY column_id) AS rn
FROM sys.COLUMNS
WHERE Object_Name(object_id) = 'MyTable')
SELECT @Sql = 'Select ' + QuoteName(name) + ' From MyTable'
FROM cte
WHERE rn = @WhichOne;
Exec(@Sql);
From http://www.daniweb.com/web-development/databases/ms-sql/threads/341388
Just so you can go shoot yourself in the foot, here's how you could do it with dynamic sql:
DECLARE @sql NVARCHAR(MAX)
select
@sql = 'SELECT ' + COLUMN_NAME + ' FROM YourTable'
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='YourTable'
AND ORDINAL_POSITION=3
exec sp_executeSQL @sql
