April 3, 2013 at 3:20 pm
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
April 4, 2013 at 12:58 am
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
April 4, 2013 at 10:06 am
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.
April 4, 2013 at 12:26 pm
;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
April 4, 2013 at 12:55 pm
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