How do I pivot date column as the first column?

I have a table using oracle SQL with hospital departments with daily input rows and this is the table columns

        select IDNO,
               DEPT_ID,
               SAMPLES_MORNING as SM,
               TESTS_MORNING   as TM,
               SAMPLES_EVENING as SE,
               TESTS_EVENING   as TE,
               sent_out        as SO,
               STAT_DATE,
               HOSPITAL_NO,
               USER_ID
          from DEPARTMENT_STATISTICS

I need to make statistics sheet get SUM of samples and tests for each hospital daily and using pivot function and the output I need like this (some columns omitted) :

I tried this query and pivot:

    SELECT * FROM
    (
      SELECT dept_id , hospital_no,STAT_DATE, SAMPLES_MORNING , TESTS_MORNING , samples_evening , TESTS_EVENING , SENT_OUT FROM DEPARTMENT_STATISTICS
    )
    PIVOT
    (
      sum(SAMPLES_MORNING) as SM , sum(TESTS_MORNING) as TM, sum(samples_evening) AS SE , sum(TESTS_EVENING) as TE , SUM(SENT_OUT) AS OUT
      FOR hospital_no in (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,21,22,41,61,81,107,108,109,110)
    );

but STAT_DATE show in the rows ,

how can I pivot STAT_DATE as the first column up the query result and up the HOSPITAL_NO also ?

Making one column from date and hospital

The most straightforward method to me would have been to simply concatenate your pivoting columns:

SELECT * FROM
(
  SELECT
    dept_id,
    to_char(stat_date, 'DD/MM/YYYY')||' - '||hospital_no AS date_and_hospital,
    SAMPLES_MORNING , TESTS_MORNING , samples_evening , TESTS_EVENING , SENT_OUT
  FROM DEPARTMENT_STATISTICS
)
PIVOT
(
  sum(SAMPLES_MORNING) as SM , sum(TESTS_MORNING) as TM, sum(samples_evening) AS SE , sum(TESTS_EVENING) as TE , SUM(SENT_OUT) AS OUT
  FOR date_and_hospital in
  (
    '01/06/2025 - 2',
    '01/06/2025 - 3',
    -- …,
    '02/06/2025 - 2',
    '02/06/2025 - 3'
    -- , …
  )
);

(as can be seen in the first SELECT of that fiddle)

Clean multiple columns handling

… But, as I discovered in the last article about Dynamic pivoting (see next §), even since Oracle 11 you can avoid concatenating your columns from the start;
still you will have to enumerate all cases, like with the concatenation above:

SELECT * FROM
(
  SELECT dept_id , hospital_no,STAT_DATE, SAMPLES_MORNING , TESTS_MORNING , samples_evening , TESTS_EVENING , SENT_OUT FROM DEPARTMENT_STATISTICS
)
PIVOT
(
  sum(SAMPLES_MORNING) as SM , sum(TESTS_MORNING) as TM, sum(samples_evening) AS SE , sum(TESTS_EVENING) as TE , SUM(SENT_OUT) AS OUT
  FOR (STAT_DATE, hospital_no) in
  (
    ('01/06/2025', 2) AS "01/06/2025 - 2",
    ('01/06/2025', 3) AS "01/06/2025 - 3",
    -- …,
    ('02/06/2025', 2) AS "02/06/2025 - 2",
    ('02/06/2025', 3) AS "02/06/2025 - 3"
    -- , …
  )
);

(second SELECT of that fiddle)

Dynamic pivoting!

Of course, given the complexity of the problem, you've probably considered a way of dynamic pivoting,
which as seen in this SO question, this Oracle forum post, or with a more satisfying PL/SQL implementation in this article,
will require some high-level language wrapping.

In PL/SQL, your EXECUTE IMMEDIATE 'SELECT * FROM …' won't return rows, but you can EXECUTE IMMEDIATE 'CREATE TABLE report …'; EXECUTE IMMEDIATE 'INSERT INTO report SELECT * FROM …', then SELECT from this populated results table:

DECLARE
  mysql varchar2(4000);
  cols varchar2(4000);
BEGIN
  -- Create a table with the column names we will want.
  
  SELECT listagg('"'||STAT_DATE||' - '||hospital_no||' - '||type||'" int', ', ') WITHIN GROUP (ORDER BY STAT_DATE, hospital_no, colpos)
  INTO cols
  FROM
    (SELECT DISTINCT STAT_DATE, hospital_no FROM DEPARTMENT_STATISTICS) s,
    (SELECT 1 colpos, 'SM' type from dual union all select 2, 'TM' from dual union all select 3, 'SE' from dual union all select 4, 'TE' from dual union all select 5, 'OUT' from dual) types
  ;
  mysql := 'CREATE TABLE report (DEPT_ID int, '||cols||')';
  EXECUTE IMMEDIATE mysql;
  
  -- Create our PIVOT.
  SELECT listagg('('''||STAT_DATE||''', '||hospital_no||')', ', ') WITHIN GROUP (ORDER BY STAT_DATE, hospital_no)
  INTO cols
  FROM
    (SELECT DISTINCT STAT_DATE, hospital_no FROM DEPARTMENT_STATISTICS) s
  ;
  mysql :=
  '
    INSERT INTO report
    SELECT * FROM
    (
      SELECT dept_id , hospital_no,STAT_DATE, SAMPLES_MORNING , TESTS_MORNING , samples_evening , TESTS_EVENING , SENT_OUT FROM DEPARTMENT_STATISTICS
    )
    PIVOT
    (
      sum(SAMPLES_MORNING) as SM , sum(TESTS_MORNING) as TM, sum(samples_evening) AS SE , sum(TESTS_EVENING) as TE , SUM(SENT_OUT) AS OUT
      FOR (STAT_DATE, hospital_no) in
      (
        '||cols||'
      )
    )
  ';
  EXECUTE IMMEDIATE mysql;
END;
/
SELECT * FROM report;

With the same result (with pretty column names as we could name them in the CREATE TABLE):

(and you've got it running as the last block of our fiddle)