oracle - merge multiple column values-CodePudding

I have following data set:

DAILY_DATA:

DEPT	CLASS	DATE	BOOK_ID	TRANSFER_RETAIL	TRANSFER_COST
100	1	10-APR	100	23.4	54
100	1	10-APR	200	0	92

I want to merge the values of TRANSFER_RETAIL AND TRANSFER_COST and add it to the row where BOOK_ID = 100

so the row will show:

DEPT	CLASS	DATE	BOOK_ID	TRANSFER_RETAIL	TRANSFER_COST
100	1	10-APR	100	23.4	146
100	1	10-APR	200	0	92

How can I do that?

CodePudding user response：

Rather than merging them in an already existing column, just add a new virtual column such as

ALTER TABLE daily_data
ADD (
      transfer_total AS ( transfer_retail transfer_cost )
    );

which won't occupy any extra space within the data segment, and just used to display the desired computed value always depending on those two columns. Btw, your current column's value of transfer_cost will be kept as orginal, and the normalisation rule for the table will be protected.

CodePudding user response：

I suggest that it would be better to create a VIEW than to modify the raw data. This avoids all risk that the update query is run twice etc. and the original data is always avaliable.

CREATE TABLE DAILY_DATA(DEPT INT,CLASS int, "DATE" DATE,BOOK_ID INT, TRANSFER_RETAIL DECIMAL(5,2),TRANSFER_COST DECIMAL(5,2));

✓

INSERT INTO DAILY_DATA 
SELECT 100, 1,to_date('2022-04-10','yyyy-mm-dd'),100,23.4,54 FROM dual UNION ALL
SELECT 100, 1,to_date('2022-04-10','yyyy-mm-dd'), 200, 0, 92 FROM dual

CREATE VIEW TOTALLED AS
SELECT
  DEPT,
  CLASS,
  "DATE",
  BOOK_ID,
    CASE WHEN BOOK_ID = 100 THEN SUM(TRANSFER_RETAIL) OVER(partition BY "DATE")
      ELSE TRANSFER_RETAIL end TRANSFER_RETAIL,
    CASE WHEN BOOK_ID = 100 THEN SUM(TRANSFER_COST) OVER(partition BY "DATE") 
      ELSE TRANSFER_COST end TRANSFER_COST
from DAILY_DATA

✓

SELECT * FROM TOTALLED;

DEPT | CLASS | DATE      | BOOK_ID | TRANSFER_RETAIL | TRANSFER_COST
---: | ----: | :-------- | ------: | --------------: | ------------:
 100 |     1 | 10-APR-22 |     200 |               0 |            92
 100 |     1 | 10-APR-22 |     100 |            23.4 |           146

db<>fiddle here

CodePudding user response：

You can use a MERGE statement to combine the rows:

MERGE INTO daily_data dst
USING (
  SELECT dept,
         class,
         "DATE",
         SUM(transfer_retail) AS transfer_retail,
         SUM(transfer_cost) AS transfer_cost
  FROM   daily_data
  WHERE  book_id = 200
  -- AND dept = 100
  -- AND class = 1
  -- AND "DATE" = DATE '2022-04-10'
  GROUP BY dept, class, "DATE"
) src
ON (    dst.book_id = 100
    AND dst.dept    = src.dept
    AND dst.class   = src.class
    AND dst."DATE"  = src."DATE")
WHEN MATCHED THEN
  UPDATE
  SET transfer_retail = dst.transfer_retail   src.transfer_retail,
      transfer_cost   = dst.transfer_cost   src.transfer_cost;

Which, for the sample data:

CREATE TABLE DAILY_DATA (DEPT, CLASS, "DATE", BOOK_ID, TRANSFER_RETAIL, TRANSFER_COST) AS
SELECT 100, 1, DATE '2022-04-10', 100, 23.4, 54 FROM DUAL UNION ALL
SELECT 100, 1, DATE '2022-04-10', 200, 0,    92 FROM DUAL;

Then after the MERGE, the table will contain:

DEPT CLASS DATE BOOK_ID TRANSFER_RETAIL TRANSFER_COST

100 1 10-APR-22 100 23.4 146

100 1 10-APR-22 200 0 92

DEPT	CLASS	DATE	BOOK_ID	TRANSFER_RETAIL	TRANSFER_COST
100	1	10-APR-22	100	23.4	146
100	1	10-APR-22	200	0	92

db<>fiddle here