How to Select SQL Server Data Using Column Ordinal Position

Ordinal column position from sys schema

You are on the right path...

DECLARE @ObjectName sysname = 'def_transaction_pt',
        @SchemaName sysname = 'dbo'

SELECT c.name As Column_Name, ROW_NUMBER() OVER(ORDER BY c.column_id) As Ordinal_Position
FROM sys.columns AS c
JOIN sys.objects AS o
    ON c.object_id = o.object_id 
JOIN sys.schemas AS s
    ON o.schema_id = s.schema_id
WHERE o.name = @ObjectName
AND s.name = @SchemaName

Note: I've only joined the sys.schemas to enable filtering using schema name,
and sys.objects to allow filtering using table/view name

After reading the comments and documentation, I've decided to adopt Larnu's suggestion about using row_number instead of the column_id directly.
Also, I've changed All_columns to Columns since it contains the columns from both tables and views.

BTW, the official documentation of information_schema.Columns describes the Ordinal_Position as "Column identification number." - which might be just the same as the Column_Id - It would require more testing to figure out that part.

BTW #2: Though you can't change the column's ordinal position using an alter table, it is possible to do using the visual designer (which in turn, drops and re-creates the table) - and if you do that, the column id for all columns is re-calculated, so it's always corresponding with the actual ordinal position (gaps aside).

how to get table name and a particular column ordinal position in entire database

SELECT table_name,column_name,ordinal_position 
FROM   information_schema.columns  
WHERE  table_catalog = 'dbSales' and column_name = 'product'

The information schema consists of a set of views that contain information about the objects defined in the current database.

How to increment column ordinal position in a while function?

Can you not just move your SELECT from sys.Columns into the body of your WHILE loop to get the one you're after? Something like this...

DECLARE @TABLENAME AS VARCHAR(128);
DECLARE @COLUMN1 AS VARCHAR(128);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 1;

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

END
;

Maybe I didn't properly understand your question, but if you already have everything written out you could just change the placement to select the column names you want.

Of course you can also add additional conditions to make sure you're getting matched columns, or columns by name, or whatever...

EDIT:

You asked about inserting the resultant query into a table. If you already have a table with a column ready to receive the query, then the answer is simple... In the body of your WHILE loop, after retrieving the desired query, simply add an INSERT command:

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    INSERT INTO <mytable> (<querycolumn>) VALUES (@SQL);
    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

If you need to create the table, you can do that in the head of the script, before declaring anything else, and do the same thing during the WHILE:

DECLARE @TABLENAME AS VARCHAR(128);
DECLARE @COLUMN1 AS VARCHAR(128);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 1;
CREATE TABLE <mytable> (<optional_column1>,<optional_column2>,<querycolumn>);

....

WHILE ....
BEGIN

    SELECT @COLUMN1 = C.Name
    FROM sys.Columns C
    WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
    AND C.column_id = @COUNTER
    ;
    SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
    Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 1';

    INSERT INTO <mytable> (<querycolumn>) VALUES (@SQL);
    ... Other Stuff ...

    SET @COUNTER = @COUNTER + 1;

Selecting column by its number

This is not exactly same as what you are trying to do. However, It is almost there. It won't select column by number, however you dont have to specify the explicit column from your real table while writing this query.

As all us suggested, you have to use the dynamic SQL. This is a little idea I created:

create table test1(name1 varchar(10), address1 varchar(10), zipcode1 varchar(10))

insert into test1 values('Test1.1','USA','12344')
insert into test1 values('Test1.2','USA','12344')
insert into test1 values('Test1.3','USA','12344')
insert into test1 values('Test1.4','USA','12344')

create table test2(name2 varchar(10), address2 varchar(10), zipcode2 varchar(10))

insert into test2 values('Test2.1','USA','12344')
insert into test2 values('Test2.2','USA','12344')
insert into test2 values('Test2.3','USA','12344')
insert into test2 values('Test2.4','USA','12344')

You see, the Table name, and the Column name are completely different in both.
Now this sql statement doesn't care about column names :

select * from
(
    select '' as T1, '' as T2, '' as T3
    union all
    select * from test1   --No matter whether it is Id, Name or description
    union all
    select * from test2   --No matter whether it is Id, Name or description
) as D
where D.T1<>''  -- your other conditions!

Only issue is, since we are using Union, you have to match the number of columns when you specify your empty columns:

select '' as T1, '' as T2, '' as T3, '' as T4, 0 as T5 -- and so on

Here's the output:

Does INSERT INTO ... SELECT ... always match fields by ordinal position?

That is correct, SQL Server does not try to do any mapping of column names since you can apply any aliases to the source data that you like. It will always reference ordinal position.

sql server select column by number

You have to use dynamic SQL to do this:

DECLARE @strSQL AS nvarchar(MAX)
DECLARE @strColumnName AS nvarchar(255)
DECLARE @iCounter AS integer 
DECLARE @curColumns AS CURSOR 


SET @iCounter = 0
SET @strSQL = N'SELECT '

SET @curColumns = CURSOR FOR 
(
    SELECT * FROM 
    (
        SELECT TOP 99999 
            COLUMN_NAME 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'T_Markers' 
        AND ORDINAL_POSITION < 4 
        ORDER BY ORDINAL_POSITION ASC 
    ) AS tempT 
)

OPEN @curColumns
FETCH NEXT FROM @curColumns INTO @strColumnName 
WHILE @@FETCH_STATUS = 0
BEGIN
    -- PRINT @strColumnName 
    IF @iCounter = 0 
        SET @strSQL = @strSQL + N'
     [' + @strColumnName + N'] ' 
    ELSE 
        SET @strSQL = @strSQL + N'
    ,[' + @strColumnName + N'] ' 
    SET @iCounter = @iCounter + 1 
FETCH NEXT FROM @curColumns INTO @strColumnName 
END
CLOSE @curColumns
DEALLOCATE @curColumns 

SET @strSQL = @strSQL + N' 
FROM T_Markers 
'

PRINT @strSQL 

Benefits Of Using SQL Ordinal Position Notation?

I'd use it:

• If you love troubleshooting
• Creating adhoc queries without intellisense

There is no upside.

SQL Server only supports in the ORDER BY anyway. Anywhere else it's an expression to be evaluated.

---

Related Topics