Post reply

Column Retrive using column id

  • February 13, 2012 at 11:00 pm

    #251551

    Hi All,

    I'm having more than 50 columns in a table.. Now the user can select any columns from that table by using the column id as input..

    declare @word nvarchar(500)

    select @word= COALESCE(@word +',',' ') + name from sys.all_columns where object_id= 1337576349 and column_id between 11 and 15

    select @word

    execute('select '+ @word +' from inventory.inventory_transaction ')

    The above query is correct? Is there is any other way for my solutions

  • February 13, 2012 at 11:05 pm

    #1446635

    I guess my only question would be... why are you doing this by column number instead of by column name?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 14, 2012 at 3:04 am

    #1446707

    Jeff Moden (2/13/2012)

    I guess my only question would be... why are you doing this by column number instead of by column name?

    +1

    I guess you're complicating things too much.

    Can you explain in detail what you're trying to achieve? Maybe there's a better way.

    -- Gianluca Sartori

  • February 14, 2012 at 3:22 am

    #1446719

    Hi all,

    this is the question asked in my previous interview.. How to select column 11 to nth from a table..

  • February 14, 2012 at 3:37 am

    #1446739

    My reply to the interviewer would be to ask why I would need to do that. Certainly not something that you need to do in queries and referencing columns by ordinal position (say in a .net structure) is considered bad practice.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 14, 2012 at 3:48 am

    #1446742

    guruprasad1987 (2/14/2012)

    Hi all,

    this is the question asked in my previous interview.. How to select column 11 to nth from a table..

    This is one of the dumbest questions I have ever seen!

    Are you sure they didn't ask how to retrieve ROWS between 11 and Nth position?

    However, this should do the trick:

    -- Declare some variables

    DECLARE @obj_id int

    DECLARE @column_list nvarchar(max)

    DECLARE @sql nvarchar(max)

    DECLARE @first_column int

    DECLARE @last_column int

    -- Initialize variables

    SELECT @obj_id = 1163151189,

    @first_column = 4,

    @last_column = 6

    -- Retrieve column list

    -- using FOR XML PATH('') concatenation

    -- Read more here: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

    SELECT @column_list =

    STUFF((

    SELECT ',' + name

    FROM sys.columns

    WHERE object_id = @obj_id

    AND column_id BETWEEN @first_column AND @last_column

    FOR XML PATH('')

    ), 1, 1, '')

    -- Build SELECT statament

    SET @sql = N'SELECT ' + @column_list + N' FROM ' + OBJECT_NAME(@obj_id)

    -- Execute statament

    EXEC(@sql)

    -- Gianluca Sartori

  • February 14, 2012 at 3:55 am

    #1446744

    Hi Gianluca Sartori,

    Thank you.. Im sure they asked for particular range of columns 11th column to nth column

    Regards

    GuruPrasad

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply