Oracle 21c

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

create table allorders (Order_Nr varchar2(10), Items varchar2(100));
insert all 
into allorders values ('O100'  ,'5X1')
into allorders values ('O100'  ,'5X1')
into allorders values ('O100'  ,'7B3')
into allorders values ('O101'  ,'3A2')
into allorders values ('O101'  ,'3A2')
select * from dual
5 rows affected

select * from allorders


ORDER_NR
ITEMS


O100
5X1

O100
5X1

O100
7B3

O101
3A2

O101
3A2


select   Order_Nr, count(*) || 'x' || Items as OrderOverview
from     allorders 
where    Order_Nr = 'O100'
group by Items, Order_Nr


ORDER_NR
ORDEROVERVIEW


O100
2x5X1

O100
1x7B3


select   max(Order_Nr) as Order_Nr
        ,listagg(count(*) || 'x' || Items, ' ') within group (order by Items) as OrderOverview
from     allorders 
where    Order_Nr = 'O100'
group by Items, Order_Nr


ORDER_NR
ORDEROVERVIEW


O100
2x5X1 1x7B3