Db2 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table tableA( ID int, SERVICE_TYPE varchar(20), CNT int)
insert into tableA values
 (12345678,'SACCOS',	0)
,(12345678,'RETAIL_SME',	0)
,(12345678,'TRANS_LOGISTIC',	0)
,(12345678,'HEALTHCARE',	0)
,(12345678,'ENERGY',	7)
,(12345678,'NGO',	0)
,(12345678,'TECH_SERVICES',	0)
,(12345678,'BUSINESS_SERVICES',	0)
,(12345678,'RELIGIOUS',	0)
,(12345678,'INSURANCE',	0)
,(12345678,'EDUCATION',	0)
,(12345678,'MICROFINANCE',	0)
,(12345678,'LOAN_REP_COUNT',	2)
,(12345678,'WATER_BILL',	0)
,(12345678,'PAY_TV',	0)
,(12345678,'MERCHPAY_OP',	1)
,(12345678,'MERCHPAY_AM',	0)
,(12345678,'BETTING',	0)
,(12345678,'AMTk_BUNDLE',	0)
,(12345678,'AMTK_AIRTIME_RECH',	1)
,(12345678,'ELECTRICITY_BILL',	0)
,(12345678,'W2B',	0)
,(12345678,'P2P_UNREG',	0)
,(12345678,'P2P_OPTK',	1)
,(12345678,'P2P_AMTK',	1)
,(12345678,'CASHOUT',	0)
,(12345678,'IMT',	0)
,(12345678,'AGRICULTURE',	0)
,(12345678,'AVIATION',	0)
,(12345678,'SPORTS',	0)
,(12345678,'CONSTRUCTION',	0)
,(12345678,'HORECA',	0)
,(12345678,'AGGREGATOR',	0)
,(12345678,'MEDIA',	0)
,(12345678,'FMCG',	0)
,(22345678,'FMCG',	0)
,(22345678,'MEDIA',	0)
,(22345678,'HORECA',	0)
;
select * from tableA order by cnt;


ID
SERVICE_TYPE
CNT


12345678
SACCOS
0

12345678
RETAIL_SME
0

12345678
TRANS_LOGISTIC
0

12345678
HEALTHCARE
0

12345678
NGO
0

12345678
TECH_SERVICES
0

12345678
BUSINESS_SERVICES
0

12345678
RELIGIOUS
0

12345678
INSURANCE
0

12345678
EDUCATION
0

12345678
MICROFINANCE
0

12345678
WATER_BILL
0

12345678
PAY_TV
0

12345678
MERCHPAY_AM
0

12345678
BETTING
0

12345678
AMTk_BUNDLE
0

12345678
ELECTRICITY_BILL
0

12345678
W2B
0

12345678
P2P_UNREG
0

12345678
CASHOUT
0

12345678
IMT
0

12345678
AGRICULTURE
0

12345678
AVIATION
0

12345678
SPORTS
0

12345678
CONSTRUCTION
0

12345678
HORECA
0

12345678
AGGREGATOR
0

12345678
MEDIA
0

12345678
FMCG
0

22345678
FMCG
0

22345678
MEDIA
0

22345678
HORECA
0

12345678
MERCHPAY_OP
1

12345678
AMTK_AIRTIME_RECH
1

12345678
P2P_OPTK
1

12345678
P2P_AMTK
1

12345678
LOAN_REP_COUNT
2

12345678
ENERGY
7


WITH RankedServices AS 
(
    SELECT
        ID,
        COALESCE(SERVICE_TYPE, 'non-user') AS SERVICE_TYPE,
        RANK() OVER (PARTITION BY ID ORDER BY COALESCE(CNT, 0) DESC) AS rnk
    FROM 
        tableA
    WHERE 
        COALESCE(CNT, 0) > 0 -- Exclude products with count 0
)
SELECT
    ID,
    MAX(CASE WHEN rnk = 1 THEN SERVICE_TYPE END) AS First_Pref_Leg,
    MAX(CASE WHEN rnk = 2 THEN SERVICE_TYPE END) AS Second_Pref_Leg,
    MAX(CASE WHEN rnk = 3 THEN SERVICE_TYPE END) AS Third_Pref_Leg
FROM 
    RankedServices
GROUP BY 
    ID;


ID
FIRST_PREF_LEG
SECOND_PREF_LEG
THIRD_PREF_LEG


12345678
ENERGY
LOAN_REP_COUNT
P2P_OPTK


WITH RankedServices AS 
(
    SELECT
        ID,
        COALESCE(SERVICE_TYPE, 'non-user') AS SERVICE_TYPE,
        dense_RANK() OVER (PARTITION BY ID ORDER BY COALESCE(CNT, 0) DESC) AS rnk
    FROM 
        tableA
    WHERE 
        COALESCE(CNT, 0) > 0 -- Exclude products with count 0
)
SELECT
    ID,
    listagg(CASE WHEN rnk = 1 THEN SERVICE_TYPE END,',') AS First_Pref_Leg,
    listagg(CASE WHEN rnk = 2 THEN SERVICE_TYPE END,',') AS Second_Pref_Leg,
    listagg(CASE WHEN rnk = 3 THEN SERVICE_TYPE END,',') AS Third_Pref_Leg
FROM 
    RankedServices
GROUP BY 
    ID;


ID
FIRST_PREF_LEG
SECOND_PREF_LEG
THIRD_PREF_LEG


12345678
ENERGY
LOAN_REP_COUNT
MERCHPAY_OP,AMTK_AIRTIME_RECH,P2P_OPTK,P2P_AMTK


select Id
  ,coalesce(First_pref_leg,'non-user') AS First_pref_leg
  ,coalesce(Second_pref_leg,'non-user') AS Second_pref_leg
  ,coalesce(Third_pref_leg,'non-user') AS Third_pref_leg
from(
  SELECT Id
    ,listagg(CASE WHEN rnk = 1  and cnt>0 THEN SERVICE_TYPE END,',') AS First_Pref_Leg
    ,listagg(CASE WHEN rnk = 2 and cnt>0 THEN SERVICE_TYPE END,',') AS Second_Pref_Leg
    ,listagg(CASE WHEN rnk = 3 and cnt>0 THEN SERVICE_TYPE END,',') AS Third_Pref_Leg
  FROM 
    ( SELECT *
        ,dense_RANK() OVER (PARTITION BY ID ORDER BY CNT DESC) AS rnk
    FROM tableA
    )AS RankedServices
  GROUP BY ID
) AS x;


ID
FIRST_PREF_LEG
SECOND_PREF_LEG
THIRD_PREF_LEG


12345678
ENERGY
LOAN_REP_COUNT
MERCHPAY_OP,AMTK_AIRTIME_RECH,P2P_OPTK,P2P_AMTK

22345678
non-user
non-user
non-user

ID	SERVICE_TYPE	CNT
12345678	SACCOS	0
12345678	RETAIL_SME	0
12345678	TRANS_LOGISTIC	0
12345678	HEALTHCARE	0
12345678	NGO	0
12345678	TECH_SERVICES	0
12345678	BUSINESS_SERVICES	0
12345678	RELIGIOUS	0
12345678	INSURANCE	0
12345678	EDUCATION	0
12345678	MICROFINANCE	0
12345678	WATER_BILL	0
12345678	PAY_TV	0
12345678	MERCHPAY_AM	0
12345678	BETTING	0
12345678	AMTk_BUNDLE	0
12345678	ELECTRICITY_BILL	0
12345678	W2B	0
12345678	P2P_UNREG	0
12345678	CASHOUT	0
12345678	IMT	0
12345678	AGRICULTURE	0
12345678	AVIATION	0
12345678	SPORTS	0
12345678	CONSTRUCTION	0
12345678	HORECA	0
12345678	AGGREGATOR	0
12345678	MEDIA	0
12345678	FMCG	0
22345678	FMCG	0
22345678	MEDIA	0
22345678	HORECA	0
12345678	MERCHPAY_OP	1
12345678	AMTK_AIRTIME_RECH	1
12345678	P2P_OPTK	1
12345678	P2P_AMTK	1
12345678	LOAN_REP_COUNT	2
12345678	ENERGY	7