By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (
customer_id int,
offer_1 varchar(10),
offer_2 varchar(10),
offer_3 varchar(10)
)
insert into t (customer_id, offer_1, offer_2, offer_3)
values (111, 'A01', '001', 'B01')
insert into t (customer_id, offer_1, offer_2, offer_3)
values (222, 'A01', 'B01', '001')
insert into t (customer_id, offer_1, offer_2, offer_3)
values (333, 'A02', '001', 'B01')
select * from t
| CUSTOMER_ID | OFFER_1 | OFFER_2 | OFFER_3 |
|---|---|---|---|
| 111 | A01 | 001 | B01 |
| 222 | A01 | B01 | 001 |
| 333 | A02 | 001 | B01 |
select count(distinct offer_combo) as distinct_offers
from (
select listagg(offer, '/') within group (order by offer) as offer_combo
from (
select customer_id, offer_1 as offer from t
union all select customer_id, offer_2 from t
union all select customer_id, offer_3 from t
) x
group by customer_id
) y
| DISTINCT_OFFERS |
|---|
| 2 |