Search code examples
sql-serverrow-numberinformation-schema

Creating an array of column names in Microsoft Server SQL

I'm fairly new to SQL, roughly a week of using it.

I'm trying to figure out a way to create an array of column names. Through research, I've found a way to select column names and a way to select an nth row. However, I need some way to combine these two.

Here is the following code for each:

Selecting columns:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'tablename'

Selecting nth row:

SELECT * FROM
(
  SELECT ROW_NUMBER() OVER(ORDER BY Starting) NUM,
  * FROM tablename
) A
WHERE NUM = 1

Is there a way to combine the two so that I can get a particular value for the nth row for the first select command (column names)?

Solution 
  • SELECT COLUMN_NAME FROM
(
  SELECT ROW_NUMBER() OVER(ORDER BY ORDINAL_POSITION) Num, COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = N'tablename'
) A
WHERE NUM = 1

    ORDINAL_POSITION is nothing but "column identification number".