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

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?

CodePudding user response：

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;