Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE jobs( job_id VARCHAR2(10), job_title VARCHAR2(100) )
INSERT INTO jobs
SELECT 'AC_ACCOUNT', 'P u b l i c  A c c o u n t a n t'                            FROM dual UNION ALL 
SELECT 'AC_FOR'    , 'F o r e n s i c  A c c o u n t a n t'                        FROM dual UNION ALL 
SELECT 'AC_MGR'    , 'A c c o u n t i n g  M a n a g e r'                          FROM dual UNION ALL 
SELECT 'AC_TAX'    , 'T a x  A c c o u n t a n t'                                  FROM dual UNION ALL 
SELECT 'AD_ASST'   , 'A d m i n i s t r a t i o n  A s s i s t a n t'              FROM dual UNION ALL 
SELECT 'AD_PRES'   , 'P r e s i d e n t'                                           FROM dual UNION ALL 
SELECT 'AD_VP'     , 'A d m i n i s t r a t i o n  V i c e  P r e s i d e n t'     FROM dual UNION ALL 
SELECT 'AP_SPC'    , 'A c c o u n t s  P a y a b l e  S p e c i a l i s t'         FROM dual UNION ALL 
SELECT 'AU_OR'     , 'A u d i t o r'                                               FROM dual UNION ALL 
SELECT 'BD_ANA'    , 'B u d g e t  A n a l y s t'                                  FROM dual UNION ALL 
SELECT 'CSS_MGR'   , 'C u s t o m e r  S u c c e s s  M a n a g e r'               FROM dual UNION ALL 
SELECT 'DB_ADM'    , 'D a t a b a s e  A d m i n i s t r a t o r'                  FROM dual UNION ALL 
SELECT 'FI_ACCOUNT', 'A c c o u n t a n t'                                         FROM dual UNION ALL 
SELECT 'FI_MGR'    , 'F i n a n c e  M a n a g e r'                                FROM dual UNION ALL 
SELECT 'HR_REP'    , 'H u m a n  R e s o u r c e s  R e p r e s e n t a t i v e'   FROM dual UNION ALL 
SELECT 'IT_PROG'   , 'S o f t w a r e  D e v e l o p e r'                          FROM dual UNION ALL 
SELECT 'MKT_COO'   , 'M a r k e t i n g  C o o r d i n a t o r'                    FROM dual UNION ALL 
SELECT 'MKT_DIG'   , 'D i g i t a l  M a r k e t i n g  M a n a g e r'             FROM dual UNION ALL 
SELECT 'MK_AST'    , 'M a r k e t i n g  A s s i s t a n t'                        FROM dual UNION ALL 
SELECT 'MK_MAN'    , 'M a r k e t i n g  M a n a g e r'                            FROM dual UNION ALL 
SELECT 'MK_REP'    , 'M a r k e t i n g  R e p r e s e n t a t i v e'              FROM dual UNION ALL 
SELECT 'NT_ADM'    , 'N e t w o r k  A d m i n i s t r a t o r'                    FROM dual UNION ALL 
SELECT 'PR_REP'    , 'P u b l i c  R e l a t i o n s  R e p r e s e n t a t i v e' FROM dual UNION ALL 
SELECT 'PU_CLERK'  , 'P u r c h a s i n g  C l e r k'                              FROM dual UNION ALL 
SELECT 'PU_MAN'    , 'P u r c h a s i n g  M a n a g e r'                          FROM dual UNION ALL 
SELECT 'SA_MAN'    , 'S a l e s  M a n a g e r'                                    FROM dual UNION ALL 
SELECT 'SA_REP'    , 'S a l e s  R e p r e s e n t a t i v e'                      FROM dual UNION ALL 
SELECT 'SH_CLERK'  , 'S h i p p i n g  C l e r k'                                  FROM dual UNION ALL 
SELECT 'ST_CLERK'  , 'S t o c k  C l e r k'                                        FROM dual UNION ALL 
SELECT 'ST_MAN'    , 'S t o c k  M a n a g e r'                                    FROM dual
30 rows affected

CREATE TABLE employees( employee_id INT, hire_date DATE, job_id VARCHAR2(10) )
INSERT INTO employees
SELECT 100, date'2003-06-17','AU_OR'      FROM dual UNION ALL 
SELECT 101, date'2005-09-21','AD_PRES'    FROM dual UNION ALL 
SELECT 102, date'2001-01-13','ST_CLERK'   FROM dual UNION ALL 
SELECT 103, date'2006-01-03','SA_MAN'     FROM dual UNION ALL 
SELECT 104, date'2007-05-21','FI_ACCOUNT' FROM dual UNION ALL 
SELECT 105, date'2005-06-25','SA_MAN'     FROM dual UNION ALL 
SELECT 106, date'2006-02-05','SA_MAN'     FROM dual
7 rows affected

--# Sample Demo for the query generated by PIVOT Clause case
SELECT *
  FROM
  (
   SELECT TO_NUMBER(TO_CHAR(hire_date,'YYYY')) AS "HIRE YEAR", job_title
     FROM employees e
     JOIN jobs j
       ON j.job_id = e.job_id
  )
 PIVOT
 (
  COUNT(*) FOR job_title IN ( 'A c c o u n t a n t' AS "A c c o u n t a n t",'A u d i t o r' AS "A u d i t o r",'P r e s i d e n t' AS "P r e s i d e n t",'S a l e s  M a n a g e r' AS "S a l e s  M a n a g e r",'S t o c k  C l e r k' AS "S t o c k  C l e r k" )
 )
 ORDER BY "HIRE YEAR"


HIRE YEAR
A c c o u n t a n t
A u d i t o r
P r e s i d e n t
S a l e s  M a n a g e r
S t o c k  C l e r k


2001
0
0
0
0
1

2003
0
1
0
0
0

2005
0
0
1
1
0

2006
0
0
0
2
0

2007
1
0
0
0
0


--# Sample Demo for the query generated by Conditional Aggregation case
SELECT "HIRE YEAR",
       SUM(  CASE  WHEN job_title =  'A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "A c c o u n t a n t",
       SUM(  CASE  WHEN job_title =  'A u d i t o r'  THEN  1  ELSE  0  END )  AS  "A u d i t o r",
       SUM(  CASE  WHEN job_title =  'P r e s i d e n t'  THEN  1  ELSE  0  END )  AS  "P r e s i d e n t",
       SUM(  CASE  WHEN job_title =  'S a l e s  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "S a l e s  M a n a g e r",
       SUM(  CASE  WHEN job_title =  'S t o c k  C l e r k'  THEN  1  ELSE  0  END )  AS  "S t o c k  C l e r k"  
  FROM
  (
   SELECT TO_NUMBER(TO_CHAR(hire_date,'YYYY')) AS "HIRE YEAR", job_title
     FROM employees e
     JOIN jobs j
       ON j.job_id = e.job_id
  )
 GROUP BY "HIRE YEAR"
 ORDER BY "HIRE YEAR"


HIRE YEAR
A c c o u n t a n t
A u d i t o r
P r e s i d e n t
S a l e s  M a n a g e r
S t o c k  C l e r k


2001
0
0
0
0
1

2003
0
1
0
0
0

2005
0
0
1
1
0

2006
0
0
0
2
0

2007
1
0
0
0
0


--# Constructing v_cols variable for the cases whenever ORA-01489 arises
SELECT RTRIM(DBMS_XMLGEN.CONVERT(
       XMLAGG(
              XMLELEMENT(e, 'SUM(  CASE  WHEN job_title =  '''||job_title||'''  THEN  1  ELSE  0  END )  AS  "'||job_title||'",')
             ).EXTRACT('//text()').GETCLOBVAL() ,1),',') AS "v_cols"
  FROM ( SELECT DISTINCT job_title
             FROM jobs j);


v_cols


SUM(  CASE  WHEN job_title =  'T a x  A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "T a x  A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'D a t a b a s e  A d m i n i s t r a t o r'  THEN  1  ELSE  0  END )  AS  "D a t a b a s e  A d m i n i s t r a t o r",SUM(  CASE  WHEN job_title =  'F i n a n c e  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "F i n a n c e  M a n a g e r",SUM(  CASE  WHEN job_title =  'P u b l i c  R e l a t i o n s  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "P u b l i c  R e l a t i o n s  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'S a l e s  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "S a l e s  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'S h i p p i n g  C l e r k'  THEN  1  ELSE  0  END )  AS  "S h i p p i n g  C l e r k",SUM(  CASE  WHEN job_title =  'S t o c k  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "S t o c k  M a n a g e r",SUM(  CASE  WHEN job_title =  'F o r e n s i c  A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "F o r e n s i c  A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'A d m i n i s t r a t i o n  V i c e  P r e s i d e n t'  THEN  1  ELSE  0  END )  AS  "A d m i n i s t r a t i o n  V i c e  P r e s i d e n t",SUM(  CASE  WHEN job_title =  'A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'S o f t w a r e  D e v e l o p e r'  THEN  1  ELSE  0  END )  AS  "S o f t w a r e  D e v e l o p e r",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  A s s i s t a n t'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  A s s i s t a n t",SUM(  CASE  WHEN job_title =  'A d m i n i s t r a t i o n  A s s i s t a n t'  THEN  1  ELSE  0  END )  AS  "A d m i n i s t r a t i o n  A s s i s t a n t",SUM(  CASE  WHEN job_title =  'C u s t o m e r  S u c c e s s  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "C u s t o m e r  S u c c e s s  M a n a g e r",SUM(  CASE  WHEN job_title =  'D i g i t a l  M a r k e t i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "D i g i t a l  M a r k e t i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'S a l e s  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "S a l e s  M a n a g e r",SUM(  CASE  WHEN job_title =  'A c c o u n t i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "A c c o u n t i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'A u d i t o r'  THEN  1  ELSE  0  END )  AS  "A u d i t o r",SUM(  CASE  WHEN job_title =  'B u d g e t  A n a l y s t'  THEN  1  ELSE  0  END )  AS  "B u d g e t  A n a l y s t",SUM(  CASE  WHEN job_title =  'H u m a n  R e s o u r c e s  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "H u m a n  R e s o u r c e s  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  C o o r d i n a t o r'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  C o o r d i n a t o r",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'P u r c h a s i n g  C l e r k'  THEN  1  ELSE  0  END )  AS  "P u r c h a s i n g  C l e r k",SUM(  CASE  WHEN job_title =  'S t o c k  C l e r k'  THEN  1  ELSE  0  END )  AS  "S t o c k  C l e r k",SUM(  CASE  WHEN job_title =  'P u b l i c  A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "P u b l i c  A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'P r e s i d e n t'  THEN  1  ELSE  0  END )  AS  "P r e s i d e n t",SUM(  CASE  WHEN job_title =  'A c c o u n t s  P a y a b l e  S p e c i a l i s t'  THEN  1  ELSE  0  END )  AS  "A c c o u n t s  P a y a b l e  S p e c i a l i s t",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'N e t w o r k  A d m i n i s t r a t o r'  THEN  1  ELSE  0  END )  AS  "N e t w o r k  A d m i n i s t r a t o r",SUM(  CASE  WHEN job_title =  'P u r c h a s i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "P u r c h a s i n g  M a n a g e r"


INSERT INTO employees
SELECT 107, date'2007-02-07','IT_PROG'    FROM dual UNION ALL 
SELECT 108, date'2002-08-17','FI_MGR'     FROM dual UNION ALL 
SELECT 109, date'2002-08-16','FI_ACCOUNT' FROM dual UNION ALL 
SELECT 110, date'2005-09-28','BD_ANA'     FROM dual UNION ALL 
SELECT 111, date'2005-09-30','FI_ACCOUNT' FROM dual UNION ALL 
SELECT 112, date'2006-03-07','AC_TAX'     FROM dual UNION ALL 
SELECT 113, date'2007-12-07','AD_VP'      FROM dual UNION ALL 
SELECT 114, date'2002-12-07','PU_MAN'     FROM dual UNION ALL 
SELECT 115, date'2003-05-18','PU_CLERK'   FROM dual UNION ALL 
SELECT 116, date'2005-12-24','AP_SPC'     FROM dual UNION ALL 
SELECT 117, date'2005-07-24','PU_CLERK'   FROM dual UNION ALL 
SELECT 118, date'2006-11-15','MKT_DIG'    FROM dual UNION ALL 
SELECT 119, date'2007-08-10','AP_SPC'     FROM dual UNION ALL 
SELECT 120, date'2004-07-18','MKT_DIG'    FROM dual UNION ALL 
SELECT 121, date'2005-04-10','NT_ADM'     FROM dual UNION ALL 
SELECT 122, date'2003-05-01','ST_MAN'     FROM dual UNION ALL 
SELECT 123, date'2005-10-10','DB_ADM'     FROM dual UNION ALL 
SELECT 124, date'2007-11-16','ST_MAN'     FROM dual UNION ALL 
SELECT 125, date'2005-07-16','ST_CLERK'   FROM dual UNION ALL 
SELECT 126, date'2006-09-28','MKT_COO'    FROM dual UNION ALL 
SELECT 127, date'2007-01-14','ST_CLERK'   FROM dual UNION ALL 
SELECT 128, date'2008-03-08','ST_CLERK'   FROM dual UNION ALL 
SELECT 129, date'2005-08-20','AD_PRES'    FROM dual UNION ALL 
SELECT 130, date'2005-10-30','ST_CLERK'   FROM dual UNION ALL 
SELECT 131, date'2005-02-16','ST_CLERK'   FROM dual UNION ALL 
SELECT 132, date'2007-04-10','AD_PRES'    FROM dual UNION ALL 
SELECT 133, date'2004-06-14','ST_CLERK'   FROM dual UNION ALL 
SELECT 134, date'2006-08-26','ST_CLERK'   FROM dual UNION ALL 
SELECT 135, date'2007-12-12','ST_CLERK'   FROM dual UNION ALL 
SELECT 136, date'2008-02-06','AC_FOR'     FROM dual UNION ALL 
SELECT 137, date'2003-07-14','ST_CLERK'   FROM dual UNION ALL 
SELECT 138, date'2005-10-26','ST_CLERK'   FROM dual UNION ALL 
SELECT 139, date'2006-02-12','ST_CLERK'   FROM dual UNION ALL 
SELECT 140, date'2006-04-06','ST_CLERK'   FROM dual UNION ALL 
SELECT 141, date'2003-10-17','ST_CLERK'   FROM dual UNION ALL 
SELECT 142, date'2005-01-29','ST_CLERK'   FROM dual UNION ALL 
SELECT 143, date'2006-03-15','ST_CLERK'   FROM dual UNION ALL 
SELECT 144, date'2006-07-09','ST_CLERK'   FROM dual UNION ALL 
SELECT 145, date'2004-10-01','SA_MAN'     FROM dual UNION ALL 
SELECT 146, date'2005-01-05','SA_MAN'     FROM dual UNION ALL 
SELECT 147, date'2005-03-10','SA_MAN'     FROM dual UNION ALL 
SELECT 148, date'2007-10-15','SA_MAN'     FROM dual UNION ALL 
SELECT 149, date'2008-01-29','SA_MAN'     FROM dual UNION ALL 
SELECT 150, date'2005-01-30','SA_REP'     FROM dual
44 rows affected

  SELECT LISTAGG( 'SUM(  CASE  WHEN job_title =  '''||job_title||'''  THEN  1  ELSE  0  END )  AS  "'||job_title||'"' , 
                  ',' )
                 WITHIN GROUP ( ORDER BY job_title ) AS "v_cols"
    FROM ( SELECT DISTINCT job_title
             FROM jobs j );
ORA-01489: result of string concatenation is too long

  SELECT LISTAGG( 'SUM(  CASE  WHEN job_title =  '''||job_title||'''  THEN  1  ELSE  0  END )  AS  "'||job_title||'"' , 
                  ',' ON OVERFLOW TRUNCATE 'THE REST IS TRUNCATED' WITHOUT COUNT )
                 WITHIN GROUP ( ORDER BY job_title ) AS "v_cols"
    INTO :v_cols
    FROM ( SELECT DISTINCT job_title
             FROM jobs j );


v_cols


SUM(  CASE  WHEN job_title =  'A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'A c c o u n t i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "A c c o u n t i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'A c c o u n t s  P a y a b l e  S p e c i a l i s t'  THEN  1  ELSE  0  END )  AS  "A c c o u n t s  P a y a b l e  S p e c i a l i s t",SUM(  CASE  WHEN job_title =  'A d m i n i s t r a t i o n  A s s i s t a n t'  THEN  1  ELSE  0  END )  AS  "A d m i n i s t r a t i o n  A s s i s t a n t",SUM(  CASE  WHEN job_title =  'A d m i n i s t r a t i o n  V i c e  P r e s i d e n t'  THEN  1  ELSE  0  END )  AS  "A d m i n i s t r a t i o n  V i c e  P r e s i d e n t",SUM(  CASE  WHEN job_title =  'A u d i t o r'  THEN  1  ELSE  0  END )  AS  "A u d i t o r",SUM(  CASE  WHEN job_title =  'B u d g e t  A n a l y s t'  THEN  1  ELSE  0  END )  AS  "B u d g e t  A n a l y s t",SUM(  CASE  WHEN job_title =  'C u s t o m e r  S u c c e s s  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "C u s t o m e r  S u c c e s s  M a n a g e r",SUM(  CASE  WHEN job_title =  'D a t a b a s e  A d m i n i s t r a t o r'  THEN  1  ELSE  0  END )  AS  "D a t a b a s e  A d m i n i s t r a t o r",SUM(  CASE  WHEN job_title =  'D i g i t a l  M a r k e t i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "D i g i t a l  M a r k e t i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'F i n a n c e  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "F i n a n c e  M a n a g e r",SUM(  CASE  WHEN job_title =  'F o r e n s i c  A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "F o r e n s i c  A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'H u m a n  R e s o u r c e s  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "H u m a n  R e s o u r c e s  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  A s s i s t a n t'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  A s s i s t a n t",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  C o o r d i n a t o r'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  C o o r d i n a t o r",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'M a r k e t i n g  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "M a r k e t i n g  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'N e t w o r k  A d m i n i s t r a t o r'  THEN  1  ELSE  0  END )  AS  "N e t w o r k  A d m i n i s t r a t o r",SUM(  CASE  WHEN job_title =  'P r e s i d e n t'  THEN  1  ELSE  0  END )  AS  "P r e s i d e n t",SUM(  CASE  WHEN job_title =  'P u b l i c  A c c o u n t a n t'  THEN  1  ELSE  0  END )  AS  "P u b l i c  A c c o u n t a n t",SUM(  CASE  WHEN job_title =  'P u b l i c  R e l a t i o n s  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "P u b l i c  R e l a t i o n s  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'P u r c h a s i n g  C l e r k'  THEN  1  ELSE  0  END )  AS  "P u r c h a s i n g  C l e r k",SUM(  CASE  WHEN job_title =  'P u r c h a s i n g  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "P u r c h a s i n g  M a n a g e r",SUM(  CASE  WHEN job_title =  'S a l e s  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "S a l e s  M a n a g e r",SUM(  CASE  WHEN job_title =  'S a l e s  R e p r e s e n t a t i v e'  THEN  1  ELSE  0  END )  AS  "S a l e s  R e p r e s e n t a t i v e",SUM(  CASE  WHEN job_title =  'S h i p p i n g  C l e r k'  THEN  1  ELSE  0  END )  AS  "S h i p p i n g  C l e r k",SUM(  CASE  WHEN job_title =  'S o f t w a r e  D e v e l o p e r'  THEN  1  ELSE  0  END )  AS  "S o f t w a r e  D e v e l o p e r",SUM(  CASE  WHEN job_title =  'S t o c k  C l e r k'  THEN  1  ELSE  0  END )  AS  "S t o c k  C l e r k",SUM(  CASE  WHEN job_title =  'S t o c k  M a n a g e r'  THEN  1  ELSE  0  END )  AS  "S t o c k  M a n a g e r",THE REST IS TRUNCATED