Need a better way to compare one column value to many column values in the same table row

user_2052524 0
Viewed 57

I have a SQL 2017 datatable structured this way:

A 1 2 3
5 10 0 0
2 0 1 0

For reporting purposes, I need a TSQL query to go through each row, get the value from column A, and compare that to each of the values in cols 1-3. If the value of col A is greater than the value of col [x], a counter should be iterated. When all comparisons for that row are complete, I record the counter's final value, then rinse and repeat for the next row.

Doing this process RBAR and cell-by-cell is not going to work. My actual table has 300+ rows, and 15 columns to which the initial value should be compared. Can anyone suggest a better way to do this?

sql-server-2017 ssms tsql sql
1 Answers

We can use CASE expressions here:

SELECT *, CASE WHEN A > [1] THEN 1 ELSE 0 END +
          CASE WHEN A > [2] THEN 1 ELSE 0 END +
          CASE WHEN A > [3] THEN 1 ELSE 0 END AS counter
FROM yourTable;
user_18632290
Related