transform more row in one row oracle sql-CodePudding

How convert more row in one oracle sql?

Example:

Create table EMP(
        emp_id number,
        emp number,
        code number,
        date_start date,
        date_end date
    )
    
    
    Insert into EMP (emp_id,emp,code,date_start,date_end) VALUES (1,100,1,sysdate,sysdate   1/24);
    Insert into EMP (emp_id,emp,code,date_start,date_end) VALUES (2,100,1,sysdate,sysdate   1/24);
    Insert into EMP (emp_id,emp,code,date_start,date_end) VALUES (3,100,2,sysdate,sysdate   1/24);
    Insert into EMP (emp_id,emp,code,date_start,date_end) VALUES (4,100,1,sysdate,sysdate   1/24);

How get resault as:

EMP    CODE_1       CODE_2
----------------------------------
100    3 (hours)    1 (hours)

3 hours is date_end - date_start

CodePudding user response：

You can use PIVOT:

SELECT *
FROM   (SELECT emp, code, 24 * (date_end - date_start) AS hours FROM emp)
PIVOT (
  SUM(hours) FOR code IN (
    1 AS code_1,
    2 AS code_2
  )
);

or conditional aggregation:

SELECT emp,
       SUM(CASE code WHEN 1 THEN 24 * (date_end - date_start) END) AS code_1,
       SUM(CASE code WHEN 2 THEN 24 * (date_end - date_start) END) AS code_2
FROM   emp
GROUP BY emp;

Which, for the sample data, both output:

EMP CODE_1 CODE_2

100 3 1

EMP	CODE_1	CODE_2
100	3	1

db<>fiddle here