Alter Table Depending on Multiple Columns

Question

I have a table that has categories like ID, a category, type, date and Shop. I want to create a new table where I drop the type column. If ID, Category, Date and Shop are the same then I drop the multiple rows and just keep one row and add a count of how many items they have bought. And if ID, category, date and shop are the same, but category isn't, the category becomes Fruit/Veg and the count is added with the number of items bought. I've highlighted what my current table looks like and what the new table will look like. But I'm not sure how to go about creating this new table.

I've started with

CREATE TABLE SINGLE_ROW AS
SELECT ID, Category, Date, Shop
FROM customer_items

But I'm not sure how to alter the table to what I need.

ID	Category	Type	Date	Shop
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

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	Shop5	1
3	Fruit	14/6/23	Shop10	1

Answer 1

You may use so called REFRESH DEFERRED MQT.
Just run the REFRESH TABLE statement when you have finished with editing customer_items to make Db2 automatically recalculate the SINGLE_ROW table with agg results based on the query in its definition.
You may create index(es) to speedup user queries on SINGLE_ROW as well if needed.

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

fiddle