Help with TSQL - a way to get the value in the Nth column of a row?

Question

I hope to find a way to get the value in the Nth column of a dataset.

Thus, for N = 6 I want

SELECT (Column6Value) from MyTable where MyTable.RowID = 14

Is there a way to do this in TSQL as implemented in SQL Server 2005? Thanks.

Answer 1

You should be able to join with the system catalog (Information_Schema.Columns) to get the column number.

Answer 2

If you know the range of n you could use a case statement

Select Case when @n = 1 then Column1Value when @n = 2 then  Column2Value end 
from MyTable

As far as I know there is no dynamic way to replace a column (or table) in a select statement without resorting to dynamic sql (in which chase you should probably refactor anyways)

Answer 3

Not sure if you're at liberty to redesign the table, but if the ordinal position of the column is significant, your data is not normalized and you're going to have to jump through lots of hoops for many common tasks.

Instead of having table MyTable with Column1... ColumnN you'd have a child table of those values you formerly stored in Column1...ColumnN each in their own row.

For those times when you really need those values in a single row, you could then do a PIVOT: http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx

Edit: My suggestion is somewhat moot. Ash clarified that it's "de-normalization by design, it's a pivot model where each row can contain one of any four data types." Yeah, that kind of design can be cumbersome when you normalize it.

Answer 4

This works:

create table test (a int, b int, c int)
insert test values(1,2,3)

declare @column_number int
set @column_number = 2

declare @query varchar(8000)

select @query = COLUMN_NAME from information_Schema.Columns
where TABLE_NAME = 'test' and ORDINAL_POSITION = @column_number

set @query = 'select ' + @query + ' from test'

exec(@query)

But why you would ever do something like this is beyond me, what problem are you trying to solve?

Answer 5

Implementation of @Mike Sharek's answer.

Declare @columnName varchar(255),
@tablename varchar(255), @columnNumber int, @SQL nvarchar(4000)
Set @tablename = 'MyTable'
Set @columnNumber = 6


Select @columnName = Column_Name from Information_SChema.columns
where Ordinal_position = @columnNumber and Table_Name = @tablename

Set @SQL  = 'select ' + @columnName + ' from ' + @tableName + ' where RowID=14'
Exec sp_Executesql @SQL

I agree with Sambo - why are you trying to do this? If you are calling the code from C# or VB, its much easier to grab the 6th column from a resultset.