DB2 SQL Developer Script to find column with Maximum Value or equal value and create another column with labels

Question

I am having a DB2 Sql Developer table with the folloing structure:

Customer_id | Electric Bill | water Bill | gas Bill | Phone Bill | Airtime Bill |
XXXXXYYYYY             45%           20%        15%         10%            10%
ZZZZZTTTTT             10%           60%        4%           6%            20%

My Goal is to have a final colmn that will tell me the preferred service per customer:

Customer_id | Electric Bill | water Bill | gas Bill | Phone Bill | Airtime Bill |Peferred Service|
XXXXXYYYYY             45%           20%        15%         10%            10%    Electric Bill
ZZZZZTTTTT             10%           60%        4%           6%            20%    Water Bill

Assume that the table has more than 2 Mil data and it is dynamic:

1. how can I do this without doing it manually
2. In case where service tie to each other like 50% and 50%, I need to make sure they are all appear in the preferred service column like 'Electric and water Bill'

NOTE:

I am using DB2 SQL Developer.

Answer 1

Could be one solution:

WITH cte AS (
SELECT Customer_id, Electric_Bill, Water_Bill,  Gas_Bill,  Phone_Bill, Airtime_Bill
     , greatest(Electric_Bill, Water_Bill,  Gas_Bill,  Phone_Bill, Airtime_Bill) AS greatest
     , CASE WHEN Electric_Bill = greatest(Electric_Bill, water_Bill,  gas_Bill,  Phone_Bill, Airtime_Bill) THEN 'Electric_Bill' ELSE '' END AS Electric_Bill_result
     , CASE WHEN water_Bill = greatest(Electric_Bill, water_Bill,  gas_Bill,  Phone_Bill, Airtime_Bill) THEN 'Water_Bill' ELSE '' END AS water_Bill_result
     , CASE WHEN gas_Bill = greatest(Electric_Bill, water_Bill,  gas_Bill,  Phone_Bill, Airtime_Bill) THEN 'Gas_Bill' ELSE '' END AS gas_Bill_result
     , CASE WHEN Phone_Bill = greatest(Electric_Bill, water_Bill,  gas_Bill,  Phone_Bill, Airtime_Bill) THEN 'Phone_Bill' ELSE '' END AS Phone_Bill_result
     , CASE WHEN Airtime_Bill = greatest(Electric_Bill, water_Bill,  gas_Bill,  Phone_Bill, Airtime_Bill) THEN 'Airtime_Bill' ELSE '' END AS Airtime_Bill_result     
  FROM bill
)
SELECT Customer_id, Electric_Bill, water_Bill,  gas_Bill,  Phone_Bill, Airtime_Bill
      , Trim(Electric_Bill_result || ' ' || Water_Bill_result || ' ' || Gas_Bill_result || ' ' || Phone_Bill_result || ' ' || Airtime_Bill_result) AS Peferred_Service
  FROM CTE

If your data contains the "%" or your really want a and between multiple identical greatest values you have to add some "cosmetics"