MySQL select all column names where condition is met for specific row

I have a table which links users to groups which is set up as below:

I would like to know if there is a method to select all groupIDs (column names) where a certain condition is met.

E.g. if I want to find all groups where user 2 has a level greater than 0 it would return (1,2,4)

It is also worth noting it cannot be done manually as there is roughly 5000 rows and 120 columns

Thanks for any help!

Answer

You can unpivot and search with union all. Assuming that the columns of your table are called grp1 to grp5:

select 1 as grp from t where userid = 2 and grp1 > 0
union all select 2 from t where userid = 2 and grp2 > 0
union all select 3 from t where userid = 2 and grp3 > 0
union all select 4 from t where userid = 2 and grp4 > 0
union all select 5 from t where userid = 2 and grp5 > 0

You should consider fixing your data model. Each user/group tuple should be stored in a separate row in a bridge table, like so:

user_groups:

userid    grp      val
     1      1        3
     1      2        2
     1      3        2
     1      4        2
     1      5        0
     ...

Then, the query is as simple as:

select grp from user_groups where userid = 2 and val > 0