In ORACLE SQL (ORACLE 19c), I'm looking for a way to get the following table content

... similarly structured (grouped) as follows:

In first column data has to be grouped by:

1. DEPARTMENT
2. JOB_START_DATE, if JOB_START_DATE is between '2021-01-01' AND '2021-12-31'
3. JOB_DESCRIPTION(s)
4. NAME(s)

In the second column, a "1" represents when persons started their job in 2021

Finally, intermediate results about the SUM of the persons who have started within a DEPARTMENT (in 2021) should be output (bottom) for each DEPARTMENT group.

Code snipped to generate the table data:

 SELECT 'Zimmer, Hans'   AS NAME,
        '1978-10-01'     AS JOB_START_DATE,
        '2021-01-31'     AS JOB_END_DATE,
        'Movie Composer' AS JOB_DESCRIPTION,
        'Score'          AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Armstrong, Louis' AS NAME,
        '1988-06-01'       AS JOB_START_DATE,
        '2021-06-30'       AS JOB_END_DATE,
        'Jazz Musician'    AS JOB_DESCRIPTION,
        'Score'            AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Davis, Miles'  AS NAME,
        '2011-10-01'    AS JOB_START_DATE,
        '2021-03-31'    AS JOB_END_DATE,
        'Jazz Musician' AS JOB_DESCRIPTION,
        'Music'         AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Coltrane, John' AS NAME,
        '2015-08-01'     AS JOB_START_DATE,
        '2021-06-30'     AS JOB_END_DATE,
        'Jazz Musician'  AS JOB_DESCRIPTION,
        'Music'          AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Cobain, Kurt'  AS NAME,
        '2021-08-01'    AS JOB_START_DATE,
        '2022-07-31'    AS JOB_END_DATE,
        'Rock Musician' AS JOB_DESCRIPTION,
        'Music'         AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Keys, Alicia'  AS NAME,
        '2021-09-01'    AS JOB_START_DATE,
        '2022-08-31'    AS JOB_END_DATE,
        'Pop Musician'  AS JOB_DESCRIPTION,
        'Music'         AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Tarantino, Quentin' AS NAME,
        '2021-03-01'         AS JOB_START_DATE,
        '2021-08-31'         AS JOB_END_DATE,
        'Movie Director'     AS JOB_DESCRIPTION,
        'Film'               AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Pitt, Brad'  AS NAME,
        '1999-10-01'  AS JOB_START_DATE,
        '2021-01-26'  AS JOB_END_DATE,
        'Movie Actor' AS JOB_DESCRIPTION,
        'Film'        AS DEPARTMENT
   FROM DUAL
   
 UNION ALL
 
 SELECT 'Nolan, Christopher' AS NAME,
        '2020-05-01'         AS JOB_START_DATE,
        '2021-03-31'         AS JOB_END_DATE,
        'Movie Director'     AS JOB_DESCRIPTION,
        'Film'               AS DEPARTMENT
   FROM DUAL;

Is it possible in ORACLE SQL to get the table data structured / grouped as wanted?

If so, how can I achieve that?

CodePudding user response：

You appear to want to use CUBE with conditional aggregation and then filter on the grouping sets:

SELECT name,
       job_description ,
       department,
       COUNT(CASE EXTRACT(YEAR FROM job_start_date) WHEN 2021 THEN 1 END)
         AS number_of_2021_jobs
FROM   table_name
GROUP BY
       CUBE(name, job_description, department)
HAVING GROUPING_ID(name, job_description, department) IN (0, 6)
ORDER BY
       department

Which, for your sample data, outputs:

NAME	JOB_DESCRIPTION	DEPARTMENT	NUMBER_OF_2021_JOBS
Nolan, Christopher	Movie Director	Film	0
Pitt, Brad	Movie Actor	Film	0
Tarantino, Quentin	Movie Director	Film	1
		Film	1
Cobain, Kurt	Rock Musician	Music	1
Coltrane, John	Jazz Musician	Music	0
Davis, Miles	Jazz Musician	Music	0
Keys, Alicia	Pop Musician	Music	1
		Music	2
Armstrong, Louis	Jazz Musician	Score	0
Zimmer, Hans	Movie Composer	Score	0
		Score	0

Note: If you want the values in the same column then concatenate the name, job_description and department columns and put new-line characters between them.

db<>fiddle here