CODEWITHSUNDEEP
sql-serverunpivot
user1904304
user1904304

Reputation: 33

Get the column name for a row where the column has the highest value

I got a table with 10 columns, the first 3 columns are used as identifiers and the other 7 columns contain numbers.

So my table structure looks like:

| ID1 | ID2 | ID3 | Data1 | Data2 | Data3 | Data4 | Data5 | Data6 | Data7 |

Now I need to get the column name (Data1 - Data7) of the column which has the highest value for the specific row.

So the data in the table may look like:

| A | B | C | 1 | 2 | 3 | 4 | 5 | 6 | 7 |
| A | B | D | 5 | 8 | 9 | 3 | 51 | 11 | 4 |
| D | A | F | 31 | 5 | 9 | 11 | 23 | 2 | 9 |

The desired outcome for this input would be:

| A | B | C | Data7 |
| A | B | D | Data5 |
| D | A | F | Data1 |

I've tried this with an unpivot query and I can get a table which shows the data ( 7, 51 and 23 ) in the last column, but I can't add the column names to this due to the group by clause which is on ID1, ID2 and ID3. Adding the pivot field to this group by clause results in showing all the data unpivoted.

Upvotes: 0

Views: 68

Answers (1)

Mark Sowul
Mark Sowul

Reputation: 10600

Use windowing functions (ROW_NUMBER) instead of the GROUP BY:

WITH Ordering AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ID1, ID2, ID3 ORDER BY Value DESC) AS OrderingValue
    FROM table
    UNPIVOT
    (
        Value FOR Source IN (Data1, Data2, Data3, Data4, Data5, Data6, Data7)
    ) up
)

SELECT *
FROM Ordering
WHERE OrderingValue = 1

Results:

ID1 ID2 ID3 Value   Source  OrderingValue
A   B   C   7       Data7   1
A   B   D   51      Data5   1
D   A   F   31      Data1   1

Obviously instead of SELECT * FROM Ordering, you can SELECT ID1, ID2, ID3, Source.

Upvotes: 1

Related Questions