Sql server select column by number

Question

Is it possible to select specific columns by the number of columns in SQL? Something like

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

thanks

Answer 1

To do this, you need to use dynamic SQL:

 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 2

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

This statement returns the third column of your table.

You will need to write an SQL transaction statement, for example

 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 3

I would highly recommend against such complex answers.

As already mentioned below, your question, you should check this answer:

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

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

Accept it and do not use numbers for columns in SELECT. It will be less efficient, less readable, and obviously will work if you change the layout.

Answer 4

Use UNPIVOT to convert columns to rows. It 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 to 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

- You need a way to select a line so that # line 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

In MySQL you can use

 SELECT * FROM mytable LIMIT 2,1; 

2 = line number you want to run.

1 = the number of rows you want to select, starting from the previous number.

I hope this was helpful.