Get MAX from row with column name (SQL)

Question

Sorry if my questin is simple, but I spent one day for googling and still can't figure out how to solve this:

I have table like:

userId A B C D E
  1    5 0 2 3 2
  2    3 2 0 7 3

And I need each MAX per row with column name:

userId MAX
  1     A
  2     D

All ideas will be much appreciated! Thanks! I use Google Big Query so my possibilities are different form MySQL as I understand, but I will try to translate if you have ideas in MySQL way.

Answer 1

You can use GREATEST:

SELECT userid, CASE GREATEST(A,B,C,D,E)
                    WHEN A THEN 'A'
                    WHEN B THEN 'B'
                    WHEN C THEN 'C'
                    WHEN D THEN 'D'
                    WHEN E THEN 'E'
               END AS MAX
FROM TableName

Result:

userId  MAX
1       A
2       D