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).

Find the row with the latest date. MAX(Date)
Look in ColX, is my value 5.4 higher than ColX's value? Y/N
Yes. Look in ColY, is my value 5.4 higher than ColY's value? Y/N
Yes. Look in ColZ, is my value 5.4 higher than ColZ's value? Y/N
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

5 isn't "a lot of columns". Do we *really* have the full picture here? Also, you say `WHERE ColX-ColZ =< 5.4`, however, for that row the respective values are `4` and `6`. `4 - 6 = -2` which is not `<= 5.4`. This is really unclear. — Thom A, Dec 05 '18 at 21:23
I have no idea what you mean by the condition on the column. — Gordon Linoff, Dec 05 '18 at 21:23
I don't think anyone can understand _where Date = MAX() AND ColX-ColZ value =< 5.4_, so please post what did you try and try to explain the conditions very well. — Ilyes, Dec 05 '18 at 21:28
`SELECT TOP 1 * FROM (SELECT ID, Date, Column = ColX UNION ALL SELECT ID, Date, ColY UNION ALL SELECT ID, Date, ColZ) AS T WHERE Column <= 5.4 ORDER BY Date DESC, Column DESC;`? Alternatively, unpivot to achieve the same sort of thing. — ZLK, Dec 05 '18 at 21:32
That's true @Larnu , I haven't given you all the columns. They will continue with values that also are increasing. ColA, ColB, ColC, ColD, ColE and so on. I try to clarify my post. — MSvelander, Dec 05 '18 at 21:43
Your last edit make the question even more unclear. What does `ColX to ColZ value =< 5.4` mean in words? And aside from that, what happens if the answer to question 4 is `Yes`? — Eric Brandt, Dec 05 '18 at 22:13
@EricBrandt I try to explain that I want to look in every column if the value of the column is equal or lower then my value(in this case 5.4) If column value is lover, then check in the next column and so on. `CASE WHEN THEN` fixed my question. But since I got 150 columns there is a lot of `WHEN THEN` rows in the SQL code. Any idea how to shorten the code down? See @Sami's answer bellow. — MSvelander, Dec 05 '18 at 22:37

score 2 · Accepted Answer · answered Dec 05 '18 at 22:03

2

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;

answered Dec 05 '18 at 22:03

Ilyes

14,640
4
29
55

1

Thanks @Sami, that did my work. Since there is 150 columns, is there some way to do the CASE without writing out every singel column? – MSvelander Dec 05 '18 at 22:22
@MSvelander - No, SQL isn't magic. The closest you'll get is writing a program to write your SQL for you. – MatBailie Dec 05 '18 at 23:44

bruceskyaus · Answer 2 · 2018-12-05T22:42:57.593

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.

score 1 · Answer 3 · answered Dec 05 '18 at 23:33

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    |
+----------+-------------------------+------+---------+

score 0 · Answer 4 · answered Dec 05 '18 at 21:53

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".

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

4 Answers4