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.
- you actually want the nth column from a table, not a dataset. It would be much more difficult to figure out if your source was a Join.Jeff Martin– Jeff Martin2009-01-12 21:59:08 +00:00Commented Jan 12, 2009 at 21:59
- Are you familiar with the PIVOT SQL expressions?dkretz– dkretz2009-01-12 22:23:14 +00:00Commented Jan 12, 2009 at 22:23
Add a comment |
5 Answers
You should be able to join with the system catalog (Information_Schema.Columns) to get the column number.
1 Comment
Ash Machine
This is what I was loking for! SELECT * FROM Information_Schema.Columns WHERE TABLE_NAME = @tablename AND ORDINAL_POSITION = 6
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?
1 Comment
Ash Machine
I am trying to solve a problem with a stored procedure using a data structure that does not follow the first normal form.
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: Link
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.
3 Comments
matt b
+1 for comment about ordinal position should not matter. You'll run into problems some day if you are relying on the position of a column.
Ash Machine
>>"but if the ordinal position of the column is significant, your data is not normalized" This is de-normalization by design, it's a pivot model where each row can contain one of any four data types.
John Rose
I almost didn't post that, since often we programmers aren't at liberty to change the table structure, but maaaan... that sounds like a table structure that's gonna cause a lot of pain.
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)
Comments
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.
1 Comment
Ash Machine
I am doing this in a stored procedure, and making no assumptions about client calling.