Getting a specific column name

  • I am trying to get the name of a column from a table from a specific value.

    For example, table1

    row C1 C2 C3

    row1 5 6 4

    row2 4 2 3

    First I find the max number (which would be 6 in row1) from a specific row, but I need the column name and not the column value. So in the end, it would return C2.

    Query to find the max number from a row (Only returns the column value):

    select (select Max(maxNum) from (VALUES (C1), (C2), (C3)) As value(maxNum)) as MaxNumber from table1 where row=row1

    Is this going in the right step or is there an easier method that could be done in a shorter amount of steps.

    Thanks

  • Try this ,

    SELECT COL.VALUE('LOCAL-NAME(.)','VARCHAR(MAX)') AS COLUMNNAME, COL.VALUE('.','VARCHAR(10)') AS VALUE

    FROM ( SELECT C1,C2,C3,C3 -- SHOULD LIST THE COLUMN NAMES EXPLICITELY ( DONT PUT * )

    FROM TABLE_NAME

    WHERE ROW_ID = @VALUE

    FOR XML PATH(''),TYPE) AS T(XMLCOL)

    CROSS APPLY

    T.XMLCOL.NODES('*') AS N(COL)

    WHERE COL.VALUE('.','VARCHAR(10)') = @MAXValue_Input

  • Getting a few errors in the query

    COL.VALUE - Cannot find either column "COL" or the user-defined function or aggregate "COL.VALUE", or the name is ambiguous.

    T - 'T' has more columns than specified in the column list

    T.XMLCOL.NODES Invalid object name 'T.SMLCODE.NODES'

    Only values I changed were "ROW_ID = @value" to the row and the value to be looked for, and "@MaxValuInput" to the max value to compare to.

    I am just unfamiliar with some of these syntax.

  • ;WITH sampledata AS

    (

    SELECT * FROM

    (VALUES

    (1,5,6,3),

    (2,4,2,5),

    (3,1,1,2),

    (4,4,2,3),

    (5,1,2,7)

    ) DATA (ID,C1,C2,C3))

    SELECT TOP(1)

    RowID

    ,colname

    ,colMax

    FROM

    sampledata s

    CROSS APPLY

    (

    VALUES (s.ID,'C1',s.C1),(s.ID,'C2',s.C2),(s.ID,'C3',s.C3)

    ) x(rowID,colName,colMax)

    ORDER BY

    ColMax DESC

     

  • Steven Willis (4/4/2013)


    ;WITH sampledata AS

    (

    SELECT * FROM

    (VALUES

    (1,5,6,3),

    (2,4,2,5),

    (3,1,1,2),

    (4,4,2,3),

    (5,1,2,7)

    ) DATA (ID,C1,C2,C3))

    SELECT TOP(1)

    RowID

    ,colname

    ,colMax

    FROM

    sampledata s

    CROSS APPLY

    (

    VALUES (s.ID,'C1',s.C1),(s.ID,'C2',s.C2),(s.ID,'C3',s.C3)

    ) x(rowID,colName,colMax)

    ORDER BY

    ColMax DESC

     

    Thanks, this works great.

    Didn't think of just putting in the column name manually. Only thing is that you will have to change the column names in the query if the column names change. But column names for the tables I am working with are not going to change, so this will suffice.

Viewing 5 posts - 1 through 4 (of 4 total)

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