which max column is true-CodePudding

I have a table with 6 fields--- customer id and 5 indicator variables.

cid, f1, f2, f3, f4, f5
101, 1,  1,  1 , 0 , 0
102, 1,  1,  0 , 0 , 0
103, 1,  0,  0 , 0 , 0

I want to get for each cust id which max field has true indicator.

Output should be

for 101 -> f3;
for 102 -> f2;
for 103 -> f1;

SQL code to reproduce the example

CREATE TABLE customer_status
(
cid int,
f1 int,
f2 int,
f3 int,
f4 int,
f5 int);
insert into customer_status values(
101, 1,  1,  1 , 0 , 0);

insert into customer_status values(
102, 1,  1,  0 , 0 , 0);

insert into customer_status values(
103, 1,  0,  0 , 0 , 0);

CodePudding user response：


SELECT
  cid,
  CASE 
    WHEN f5 = 1 THEN 'f5'
    WHEN f4 = 1 THEN 'f4'
    WHEN f3 = 1 THEN 'f3'
    WHEN f2 = 1 THEN 'f2'
    WHEN f1 = 1 THEN 'f1'
    ELSE ' '
    END
    AS result
FROM customer_status

CodePudding user response：

If your f values are only ones, you can sum up all fs and transform the result into a string:

SELECT cid, 
       CONCAT('f', f1 f2 f3 f4 f5)
FROM customer_status

Check the demo here.