sql server select column by number

Question

Can I select specific columns by the number of the columns in SQL? Something like

SELECT columns(0), columns(3), columns(5), columns(8) FROM TABLE

Answer 1

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'myTable' AND ORDINAL_POSITION = '3'

This statement returns the third column of your table

You would need to write a transact SQL statement like

DECLARE @columnname nvarchar(100), @sql nvarchar(500)

SELECT @columnname = ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = 'myTable' AND ORDINAL_POSITION = '3'

SET @sql = 'SELECT ' + @columnname + ' FROM mytable'

EXEC @sql

Answer 2

I would highly recommend against such complicated answers.

As others already pointed out below your question, you should check out this answer instead:

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

The SQL specification is not built for dynamic schema eiher in DDL or DML.

Accept it and do not use numbers for columns in the SELECT. It will be less performant, less readable and will obviously fail if you change the schema.

Answer 3

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 

Answer 4

Use UNPIVOT to convert columns to rows. Goes something like this:

F1 F2 F3 F4 F5 F6 F7 F8 F9 F10

124000 124001 124002 124003 124004 124005 124006 124007 124008 124009

-- Converts columns into rows in table #JobNos1

SELECT JobNo INTO #JobNos1 FROM

(SELECT F1, F2, F3, F4, F5, F6, F7, F8, F9, F10 FROM #YourTable) AS cp

UNPIVOT

(JobNo FOR JobNos IN (F1, F2, F3, F4, F5, F6, F7, F8, F9, F10)) AS up

124000

124001

124002

124003

124004

124005

124006

124007

124008

124009

-- It needs a way to select to row so Row # is added to #JobNos2

CREATE TABLE #JobNos2(JobNo int, Row int)

INSERT INTO #JobNos2

SELECT JobNo, ROW_NUMBER() OVER (ORDER BY (SELECT 100)) AS Row from #JobNos1

Then you can do something like this:

SET @jobno = SELECT JobNo from #JobNos2 where Row = @SomeRowNumber

Answer 5

i did with this query

declare @colCoun int
SELECT  @colCoun =COUNT(*)   -- get column count in your table
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_catalog = 'YOUR_DB_NAME'    AND table_name = 'YOUR_TABLE'

CREATE TABLE #temp([1] VARCHAR(max))

if(@colCoun>1) 
BEGIN           
DECLARE @cnt INT = 2;
WHILE @cnt <= @colCoun    
 BEGIN
 Exec ('ALTER TABLE #temp ADD ['+ @cnt +'] varchar(max)')
 SET @cnt = @cnt + 1;
 END
END

insert into #temp
select * from YOUR_TABLE

select [1],[2] from #temp -- select your own column number in range of table