Access columns of a table by index instead of name in SQL Server stored procedure

Question

Is there a way to access columns by their index within a stored procedure in SQL Server?

The purpose is to compute lots of columns. I was reading about cursors, but I do not know how to apply them.

Let me explain my problem:

I have a row like:

field_1 field_2 field_3 field_4 ...field_d  Sfield_1 Sfield_2 Sfield_3...Sfield_n
1       2       3       4          d        10       20       30         n

I need to compute something like (field_1*field1) - (Sfield_1* Sfiled_1) / more...

So the result is stored in a table column d times.

So the result is a d column * d row table.

As the number of columns is variable, I was considering making dynamic SQL, getting the names of columns in a string and splitting the ones I need, but this approach makes the problem harder. I thought getting the column number by index could make life easier.

Possible duplicate of [Is it possible to select sql server data using column ordinal position](http://stackoverflow.com/questions/368505/is-it-possible-to-select-sql-server-data-using-column-ordinal-position) — Ciro Santilli OurBigBook.com, Oct 07 '15 at 05:40

score 9 · Answer 1 · answered Feb 03 '11 at 05:37

9

No, you can not use the ordinal (numeric) position in the SELECT clause.

Only in the ORDER BY clause can you use the ordinal position, because it's based on the column(s) specified in the SELECT clause.

answered Feb 03 '11 at 05:37

OMG Ponies

325,700
82
523
502

@darkcminor: Reference the columns by name. Use dynamic SQL to get a list of column names if you like, but column order is irrelevant to a database. I forgot to mention that some databases do support ordinals in the GROUP BY and HAVING. The other alternative is to redesign the columns to support your needs. – OMG Ponies Feb 03 '11 at 05:43
I have tried SELECT @column = COALESCE(@column+ ',', '') +COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE(TABLE_NAME = 'TEMP' ) that gives me the names separated by ',' but as i said, to split and get values of several columns is getting harder, could be feasible to use pivot or something like that? – edgarmtze Feb 03 '11 at 05:56
@darkcminor: That's really a different question, and a [common one - see this for an alternative](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) – OMG Ponies Feb 03 '11 at 07:15
Not really, I have already done the split but, i was searching for an easier method, thats why I thought in access columns by index, any idea?. Thanks – edgarmtze Feb 03 '11 at 13:27

score 4 · Accepted Answer · edited May 23 '17 at 12:24

First, as OMG Ponies stated, you cannot reference columns by their ordinal position. This is not an accident. The SQL specification is not built for dynamic schema either in DDL or DML.

Given that, I have to wonder why you have your data structured as you do. A sign of a mismatch between schema and the problem domain rears itself when you try to extract information. When queries are incredibly cumbersome to write, it is an indication that the schema does not properly model the domain for which it was designed.

However, be that as it may, given what you have told us, an alternate solution would be something like the following: (I'm assuming that field_1*field1 was meant to be field_1 * field_1 or field_1 squared or Power( field_1, 2 ) )

Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
Union All Select 2, field_2, Sfield_2, Sfiled_2
...
Union All Select n, field_n, Sfield_n, Sfiled_n

Now your query looks like:

With Inputs As
    (
    Select 1 As Sequence, field_1 As [Field], Sfield_1 As [SField], Sfiled_1 As [SFiled]
    Union All Select 2, field_2, Sfield_2, Sfiled_2
    ....
    )
    ,  Results As
    (
    Select Case
            When Sequence = 1 Then Power( [Field], 2 ) - ( [SField] * [SFiled] ) 
            Else 1 / Power( [Field], 2 ) - ( [SField] * [SFiled] ) 
            End
            As Result
    From Inputs
    )
Select Exp( Sum( Log( Result ) ) )
From Results

Thanks, I have a table that way because it was generated by a longer query that returns a single row with D*N columns, Now I have to take some columns and make some operations, How do I use the code you provided in my store procedure?, As there are more field_x...I'm trhinking in dynamic sql... Like @template = Sequence, field_1 As [Field],field_2 AS [Field2],..., field_d AS [Fieldd], Sfield_1 As [SField], Sfied_2 As [SField_2],...,Sfield_n As [SField_n], in a string, and then replace dynamically in the code you provided. How can I do that?, could you explain me, a bit the code? Thank you — edgarmtze, Feb 03 '11 at 13:48
@darkcminor - First, I would look at the source of the longer query with D*N columns and see if the raw data is structured this way. Perhaps it is being assembled this way from a normalized structure. If that were the case, then obviously using the normalized structure would be easier. Second, the key to the solution is `Union All` query. Split the list of columns into a Select statement per `N` and add a sequence. Thus, given what you presented in your post, I would use something other than T-SQL to parse the string into the Union All query. Once you have that, the query should be easier. — Thomas, Feb 03 '11 at 15:56

score 0 · Answer 3 · answered Oct 27 '16 at 08:40

This might not be the most elegant or efficient but it works. I am using it to create a new table for faster mappings between data that I need to parse through all the columns / rows.

DECLARE @sqlCommand varchar(1000)
DECLARE @columnNames TABLE (colName varchar(64), colIndex int)
DECLARE @TableName varchar(64) = 'YOURTABLE' --Table Name
DECLARE @rowNumber int = 2 -- y axis
DECLARE @colNumber int = 24 -- x axis

DECLARE @myColumnToOrderBy varchar(64) = 'ID' --use primary key

--Store column names in a temp table
INSERT INTO @columnNames (colName, colIndex)
SELECT COL.name AS ColumnName, ROW_NUMBER() OVER (ORDER BY (SELECT 1))
    FROM sys.tables AS TAB
    INNER JOIN sys.columns AS COL ON COL.object_id = TAB.object_id
    WHERE TAB.name = @TableName
    ORDER BY COL.column_id;

DECLARE @colName varchar(64)
SELECT @colName = colName FROM @columnNames WHERE colIndex = @colNumber

--Create Dynamic Query to retrieve the x,y coordinates from table
SET @sqlCommand = 'SELECT ' + @colName + ' FROM (SELECT ' + @colName + ', ROW_NUMBER() OVER (ORDER BY ' + @myColumnToOrderBy+ ') AS RowNum FROM ' + @tableName + ') t2 WHERE RowNum = ' + CAST(@rowNumber AS varchar(5))
EXEC(@sqlCommand)

Access columns of a table by index instead of name in SQL Server stored procedure

3 Answers3

Linked

Related