Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH CUSTOMER_PROFESSION AS (
SELECT 1 CUST_NUMBER , 'Mike' CUST_NAME , 'Teacher' CUST_PROFESSION FROM DUAL UNION ALL
SELECT 1             ,   'Mike'         , 'Engineer' 		    FROM DUAL UNION ALL
SELECT 2             ,   'Jack'         , 'Driver' 		    FROM DUAL UNION ALL
SELECT 3             ,   'Charlie'      , 'Swimmer' 		    FROM DUAL UNION ALL
SELECT 4             ,   'Negin'        , 'Engineer' 	            FROM DUAL UNION ALL
SELECT 5             ,   'Zahara'       , 'Manager'                 FROM DUAL )
select cust_number,
       '['||cust_name||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number;
ORA-00979: not a GROUP BY expression

WITH CUSTOMER_PROFESSION AS (
SELECT 1 CUST_NUMBER , 'Mike' CUST_NAME , 'Teacher' CUST_PROFESSION FROM DUAL UNION ALL
SELECT 1             ,   'Mike'         , 'Engineer' 		    FROM DUAL UNION ALL
SELECT 2             ,   'Jack'         , 'Driver' 		    FROM DUAL UNION ALL
SELECT 3             ,   'Charlie'      , 'Swimmer' 		    FROM DUAL UNION ALL
SELECT 4             ,   'Negin'        , 'Engineer' 	            FROM DUAL UNION ALL
SELECT 5             ,   'Zahara'       , 'Manager'                 FROM DUAL )
select cust_number,
       '['||MAX(cust_name)||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number;


CUST_NUMBER
CUST_PROFESSION


1
[Mike:Engineer,Teacher]

2
[Jack:Driver]

3
[Charlie:Swimmer]

4
[Negin:Engineer]

5
[Zahara:Manager]


WITH CUSTOMER_PROFESSION AS (
SELECT 1 CUST_NUMBER , 'Mike' CUST_NAME , 'Teacher' CUST_PROFESSION FROM DUAL UNION ALL
SELECT 1             ,   'Mike'         , 'Engineer' 		    FROM DUAL UNION ALL
SELECT 2             ,   'Jack'         , 'Driver' 		    FROM DUAL UNION ALL
SELECT 3             ,   'Charlie'      , 'Swimmer' 		    FROM DUAL UNION ALL
SELECT 4             ,   'Negin'        , 'Engineer' 	            FROM DUAL UNION ALL
SELECT 5             ,   'Zahara'       , 'Manager'                 FROM DUAL )
select cust_number,
       '['||cust_name||':'||listagg(cust_profession,',')  within group (order by cust_number)||']' Cust_profession 
from CUSTOMER_PROFESSION 
group by cust_number, cust_name;


CUST_NUMBER
CUST_PROFESSION


1
[Mike:Engineer,Teacher]

2
[Jack:Driver]

3
[Charlie:Swimmer]

4
[Negin:Engineer]

5
[Zahara:Manager]
CUST_NUMBER	CUST_PROFESSION
1	[Mike:Engineer,Teacher]
2	[Jack:Driver]
3	[Charlie:Swimmer]
4	[Negin:Engineer]
5	[Zahara:Manager]