CODEWITHSUNDEEP
db2alter
Pop23
Pop23

Reputation: 11

Alter Table Depending on Multiple Columns

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

Upvotes: 0

Views: 88

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

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

Upvotes: 0

Related Questions