how to get a column index value in SQL

_{Tuesday, November 15, 2011 6:43 AM}

Hi All,

I have a small problem.

I have a set of rows in table. I need to get the exact column value from the set of rows by providing the column number. Example say,

i need to get the column[34] value like an array. Say Array[30] will give the value of the 30 th item. Similarly i need to do the same thing in SQL. Get the SQL table column values to a array and get the 34 th column.

Any idea how i can perform this in SQL?

Please help me this is very urgent

Thanks

Lucki

_{Tuesday, November 15, 2011 8:12 AM ✅Answered}

In case your have a table like this:

Table Name: arrayOfColumns

VALUE

1

3

9

20

Each row contains the value of a specified column

Use this function to retrieve the value with a particular column index

create function GetValue(@col_index int)
returns int
begin
    declare @result int;
    with col_array as
        (
            select value, ROW_NUMBER() over (order by value) as col_index
                from arrayOfColumns
        )
        select @result = col_array.value
            from col_array
            where col_array.col_index = @col_index;
    return @result;
end

Then you can test this function with:

declare @i int;
select @i = dbo.GetValue(2);
select @i;

The result will be "3"

You can then use the function like this

insert into Employee( Date, "value", dbo.GetValue(30), dbo.GetValue(45))

I can't use zero-based index value because "ROW_NUMBER()" function can not return 0 based values

_{Tuesday, November 15, 2011 6:55 AM}

 SQL server does not have arrays.Can you clear a bit more why do you want this???

Here is the query to get the column name and column ID

select name,column_id from sys.columns where object_id=object_id('<Tablename>')

Thanks and regards, Rishabh , Microsoft Community Contributor

_{Tuesday, November 15, 2011 6:56 AM}

HI Lucki  !

You can get the column Name by identiyfing the ORDINAL_POSITION;

USE AdventureWorks
GO
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = 'AdventureWorks' AND TABLE_SCHEMA = 'HumanResources' AND TABLE_NAME = 'Employee' AND ORDINAL_POSITION = 5

Please let me know if this doesn’t work for you. Hope I have answered you correctly.
 
Thanks,
Hasham

_{Tuesday, November 15, 2011 7:07 AM}

Thanks for your replies guys.

why i need this is i have a series of SQL statements to be inserted with values to a single SQL table. There is a logic to get the position of the index number. The insert statement is like this.

insert into Employee( Date, "value", value[30], value[45])

insert into Employee( Date, "value2", value[30], value[45])

I need to write this in a SSIS script task. thats y i needed to write these piece of logic in SQL.

Can anyone please help? I am so stuck with this.

thanks

Lucki

_{Tuesday, November 15, 2011 7:16 AM}

Hi Lucki,

How do you determine which row the value should be returned? You need not only the column index but also the row index as well

So it should be like:

insert into Employee( Date, "value", value[30][30], value[30][45])

In case "value" are a table.

But TSQL does not support syntax like this:

table_name[row_index][column_index]

So you may have to write you own a function like this to retrieve value from a table with only row_index and column_index

GetValue(@table_name varchar(200), row_index int, column_index int)

_{Tuesday, November 15, 2011 7:26 AM}

Hi Eric,

What i meant by value[30] is not column number 30. IT is the 30 th index value in the array of columns.

Any idea how i can get this? Put the values to a array type and then get the value from it and pass it to the insert statement.

I have no idea at all. :(

Please help

Thanks

Lucki

_{Tuesday, November 15, 2011 7:31 AM}

How do you determine which row the column belongs?

_{Tuesday, November 15, 2011 7:41 AM}

Hi Eric,

If i have the option of putting the values to an array type of a thing then i do not need the row number. Say i have the price column values in rows like this,

1,

2,

3,

4

If i can put this to a array type of a thing it would be 1,2,3,4 so if i access the price[2] column it would be 3.

This is what i need to do.

Hope this helps you. Any idea how i can do this?

Thanks

Lucki