Sql Merge Two Rows With Same Id But Different Column Values (Oracle)

Merge Rows with Same ID but Different Column Values in Oracle SQL

I think you want aggregation:

SELECT CTR_NMBR, EXEC_ID,
       MAX(CASE WHEN CODE = '2_EXEC_ZERO' THEN 2
           END) AS Exclude_Zero,
       MAX(CASE WHEN CODE = '3_EXEC_MAT' THEN 3
           END) AS Exclude_Mat
FROM EXCLUDED
GROUP BY CTR_NMBR, EXEC_ID;

SQL Merge two rows with same ID but different column values (Oracle)

Please read my Comment first - you shouldn't even think about doing this unless it is ONLY for reporting purposes, and you want to see how this can be done in plain SQL (as opposed to the correct solution, which is to use your reporting tool for this job).

The second format is easiest, especially if you don't care about the order in which the colors appear:

select   id, listagg(colour, ', ') within group (order by null)
from     table1
group by id

order by null means order randomly. If you want to order by something else, use that in order by with listagg(). For example, to order the colors alphabetically, you could say within group (order by colour).

For the first format, you need to have an a priori limit on the number of columns, and how you do it depends on the version of Oracle you are using (which you should always include in every question you post here and on other discussion boards). The concept is called "pivoting"; since version 11, Oracle has an explicit PIVOT operator that you can use.

Oracle SQL merging rows with same id and combining column results

There is no need for a self join, each of the columns other than id can use an aggregate function:

• For the data1,data2,data3 columns, use SUM for obvious reasons

• For the dt column (date is a reserved word in oracle and should not be used as a column name), use MIN or MAX (depending on your requirements). If you do not use an aggregate function you need to include this column in the GROUP BY clause and it could show multiple rows if different dates exist for the same id.

• For the name column, use MIN or MAX (depending on your requirements). If you do not use an aggregate function you need to include this column in the GROUP BY clause and it could show multiple rows if different names exist for the same id.

-- create test data
CREATE TABLE tablea (dt,  id, name, data1,data2,data3) AS
(
SELECT TO_DATE('06.08.22','DD.MM.YY'),  12345,  'Name1' ,10,    5,  5 FROM DUAL UNION ALL
SELECT TO_DATE('06.08.22','DD.MM.YY'),  12345,  'name1' ,15,    3,  2 FROM DUAL UNION ALL
SELECT TO_DATE('06.08.22','DD.MM.YY'),  34567,  'name2' ,6, 1,  5 FROM DUAL UNION ALL
SELECT TO_DATE('06.08.22','DD.MM.YY'),  45678,  'name3' ,2, 2,  4 FROM DUAL
);


SELECT
  MIN(dt)    AS min_dt,
  id,
  MIN(name)  AS min_name,
  SUM(data1) AS data1,
  SUM(data2) AS data2,
  SUM(data3) AS data3
  FROM
  tablea
 GROUP BY
  id;

MIN_DT              ID MIN_N      DATA1      DATA2      DATA3
----------- ---------- ----- ---------- ---------- ----------
06-AUG-2022      45678 name3          2          2          4
06-AUG-2022      12345 Name1         25          8          7
06-AUG-2022      34567 name2          6          1          5

Two rows with the same id and two different values, getting the second value into another column

Not sure whether order matters to you. Here is one way:

SELECT MIN(Value), MAX(Value), ID
FROM Table
GROUP BY ID;

How to merge values of two rows into single row for same id?

You can use sum or max:

select id,max(no1),max(no2) from tab_name group by id;

or

select id,sum(no1),sum(no2) from tab_name group by id;

---

Related Topics