Db2 11.1

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

CREATE TABLE customer_items AS 
(
SELECT *
FROM
(
VALUES 
  (1, 'Fruit', 'Apple 	  ', '1/2/23 ', 'Shop3 ')
, (1, 'Fruit', 'Banana 	  ', '1/2/23 ', 'Shop3 ')
, (1, 'Fruit', 'Strawberry', '4/2/23 ', 'Shop3 ')
, (2, 'Fruit', 'Apple 	  ', '15/2/23', 'Shop1 ')
, (2, 'Veg 	', 'Carrot 	  ', '15/2/23', 'Shop1 ')
, (3, 'Fruit', 'Banana 	  ', '14/6/23', 'Shop5 ')
, (3, 'Fruit', 'Banana 	  ', '14/6/23', 'Shop10')
) T (ID, Category, Type, Date, Shop)
) WITH DATA
CREATE TABLE SINGLE_ROW AS
(
SELECT 
    ID
  , LISTAGG (DISTINCT Category, '/') AS CATEGORY
  , Date
  , Shop
  , COUNT (1) AS COUNT
FROM customer_items
GROUP BY ID, Date, Shop
) DATA INITIALLY DEFERRED REFRESH DEFERRED
REFRESH TABLE SINGLE_ROW
SELECT * FROM SINGLE_ROW


ID
CATEGORY
DATE
SHOP
COUNT


1
Fruit
1/2/23
Shop3
2

1
Fruit
4/2/23
Shop3
1

2
Fruit/Veg  
15/2/23
Shop1
2

3
Fruit
14/6/23
Shop10
1

3
Fruit
14/6/23
Shop5
1

ID	CATEGORY	DATE	SHOP	COUNT
1	Fruit	1/2/23	Shop3	2
1	Fruit	4/2/23	Shop3	1
2	Fruit/Veg	15/2/23	Shop1	2
3	Fruit	14/6/23	Shop10	1
3	Fruit	14/6/23	Shop5	1