How can I get column name based on row value in SQL Server?

Question

I got an lookup table with a lot of columns with values. How can I get this output from the input using Microsoft SQL Server?
(Basically select the column name where Date = MAX() AND ColX to ColZ value =< 5.4).

1. Find the row with the latest date. MAX(Date)
2. Look in ColX, is my value 5.4 higher than ColX's value? Y/N
3. Yes. Look in ColY, is my value 5.4 higher than ColY's value? Y/N
4. Yes. Look in ColZ, is my value 5.4 higher than ColZ's value? Y/N
5. No. Output Column ColY AS Column

Input

ID        Date                 ColX    ColY    ColZ
-----------------------------------------------------
79185673  2018-11-28 00:00:00     3       5       7
79185673  2018-12-02 00:00:00     2       4       6
79185673  2018-12-04 00:00:00     4       5       6

Output

ID        Date                 Column 
--------------------------------------
79185673  2018-12-04 00:00:00    ColY

Answer 1

We'll take this in steps. Here's the data setup:

DECLARE @table TABLE
  (
    ID   INTEGER  NOT NULL
   ,Date DATETIME NOT NULL
   ,ColX INTEGER  NOT NULL
   ,ColY INTEGER  NOT NULL
   ,ColZ INTEGER  NOT NULL
  );
INSERT INTO @table
  (ID,Date,ColX,ColY,ColZ)
VALUES
  (79185673, '2018-11-28T00:00:00', 3, 5, 7);
INSERT INTO @table
  (ID,Date,ColX,ColY,ColZ)
VALUES
  (79185673, '2018-12-02T00:00:00', 2, 4, 6);
INSERT INTO @table
  (ID,Date,ColX,ColY,ColZ)
VALUES
  (79185673, '2018-12-04T00:00:00', 4, 5, 6);

First, we'll find the record with the maximum date.

SELECT TOP (1)
  *
FROM
  @table
ORDER BY
  [Date] DESC

+----------+-------------------------+------+------+------+
|    ID    |          Date           | ColX | ColY | ColZ |
+----------+-------------------------+------+------+------+
| 79185673 | 2018-12-04 00:00:00.000 |    4 |    5 |    6 |
+----------+-------------------------+------+------+------+

So that forms our base data set. From there, we want to UNPIVOT to get all of the column values into a single column. You'll have to type out all of the other column names in the UNPIVOT, but you can probably get SSMS to do some of that scripting for you by just using a default SELECT TOP N ROWS query and copying and pasting the column names from there.

SELECT
  *
FROM
  (
    SELECT
      TOP (1)
      *
    FROM
      @table
    ORDER BY
      [Date] DESC
  ) AS d
  UNPIVOT
  (
    Nums
    FOR ColName IN (ColX, ColY, ColZ)
  ) AS p

+----------+-------------------------+------+---------+
|    ID    |          Date           | Nums | ColName |
+----------+-------------------------+------+---------+
| 79185673 | 2018-12-04 00:00:00.000 |    4 | ColX    |
| 79185673 | 2018-12-04 00:00:00.000 |    5 | ColY    |
| 79185673 | 2018-12-04 00:00:00.000 |    6 | ColZ    |
+----------+-------------------------+------+---------+

According to the comments, the numbers are always increasing across the columns, so we can sort by them safely and maintain the original order. But we only care about numbers that are smaller than the target number, 5.4 in this example. So that's our WHERE clause. And we want the largest number that's less than 5.4, so we'll be using a descending ORDER BY clause. We only want that single value, so we only need the TOP (1) in our final results.

DECLARE @target DECIMAL(5,1) = 5.4;

SELECT TOP (1)
  *
FROM
  (
    SELECT
      TOP (1)
      *
    FROM
      @table
    ORDER BY
      [Date] DESC
  ) AS d
  UNPIVOT
  (
    Nums
    FOR ColName IN (ColX, ColY, ColZ)
  ) AS p
WHERE
  p.Nums < @target
ORDER BY
  p.Nums DESC;

+----------+-------------------------+------+---------+
|    ID    |          Date           | Nums | ColName |
+----------+-------------------------+------+---------+
| 79185673 | 2018-12-04 00:00:00.000 |    5 | ColY    |
+----------+-------------------------+------+---------+

Answer 2

Column names are fixed in the DDL are meta data, not values stored within the rows. You can view the column names by selecting from the information_schema.columns table. However, to implement the logic you are requesting can be done using a CASE statement in SQL.

Assuming the table name is table1, try the following query:

declare @compare_value decimal(2,1);
@compare_value = 5.4;

select
t.ID,
t.Date,
case when t.colX <= @compare_value then
    case when t.ColY <= @compare_value then
        case when t.ColZ <= @compare_value then
            'ColZ'
        else
            'ColY'
        end
    else
        'ColY'
    end
else
    'ColX'
end as "Column"
from table1 t
where t.date = (
                   select max(t1.date)
                   from table1 t1
                   where t1.ID = t.ID
               );

A word of caution: Already the code is nested to 3 levels, and will need to be nested for each column (perhaps to 150 levels)! That is some serious spagetti code. It will work, but will look messy. If the data volume is huge then performance could also be an issue as SQL is not really that useful for complex logic. You would be better off using SQL for just selecting the data you want and using a stored procedure or supplying it via an ODBC connection to say .NET or Python. Then do your complex logic processing there.

Answer 3

Is this what are you looking for

SELECT TOP 1 *,
       CASE WHEN 5.4  > ColZ 
            THEN 'ColZ'
            WHEN 5.4 > ColY
            THEN 'ColY'
            WHEN 5.4 > ColX
            THEN 'ColX'
      END [Column]
FROM T
ORDER BY [Date] DESC;

Answer 4

I doubt this is what the OP is after, based on their comments, but based on their question, this answer would be:

DECLARE @MyValue decimal(2,1) = 5.4

WITH CTE AS(
    SELECT ID,
           [Date],
           ColX,Coly,ColZ,
           ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS RN --PARTITION BY ID?
    FROM TheirTable)
SELECT ID,
       [Date],
       CASE WHEN ColZ < @MyValue THEN 'ColZ'
            WHEN ColY < @MyValue THEN 'ColY'
            WHEN ColX < @MyValue THEN 'ColX'
       END AS [Column]
FROM CTE
WHERE RN = 1;

Their comment (under the question) is somewhat leading, but after updating their question they still only have 5 columns, so I'm going to assume that 5 is what they really have. With no real definitive explanation either, this my my "best guess".