I need to Pivot date/month rows to column.

The only way I can see this - without the use of a Stored Procedure that will actually generate the statement I wrote by hand out of the very yearmonth names you use in "Month" - is what you can see below:

WITH
-- input data, but yearmonth literals changed so that they order correctly
indata(mth,department,job,fte) AS (
          SELECT '2023-08',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-09',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-10',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-11',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-12',1017,'Sourcing Consultant',1                                                                                    
UNION ALL SELECT '2024-01',1017,'Sourcing Consultant',1
UNION ALL SELECT '2024-02',1017,'Sourcing Consultant',1
UNION ALL SELECT '2024-03',1017,'Sourcing Consultant',1
)
-- end of input, query starts here ... 
SELECT
  department
, job
, MAX(CASE mth WHEN '2023-08' THEN fte END) AS "2023-08"
, MAX(CASE mth WHEN '2023-09' THEN fte END) AS "2023-09"
, MAX(CASE mth WHEN '2023-10' THEN fte END) AS "2023-10"
, MAX(CASE mth WHEN '2023-11' THEN fte END) AS "2023-11"
, MAX(CASE mth WHEN '2023-12' THEN fte END) AS "2023-12"
, MAX(CASE mth WHEN '2024-01' THEN fte END) AS "2024-01"
, MAX(CASE mth WHEN '2024-02' THEN fte END) AS "2024-02"
, MAX(CASE mth WHEN '2024-03' THEN fte END) AS "2024-03"
FROM indata
GROUP BY 
  department
, job
-- out  department |         job         | 2023-08 | 2023-09 | 2023-10 | 2023-11 | 2023-12 | 2024-01 | 2024-02 | 2024-03 
-- out ------------+---------------------+---------+---------+---------+---------+---------+---------+---------+---------
-- out        1017 | Sourcing Consultant |       1 |       1 |       1 |       1 |       1 |       1 |       1 |       1

If you want the stored procedure, shout. I'll try to paste its source here.

Hi Macrothesane.
Thanks for answering my query. Yes, it does answer my question. Only problem is I need to go in every year and tweak the query/code to remove prior year 12 months and add new 5th year's 12 month. I was hoping to avoid that annual change. But still thanks for responding!!!

Can the following help you avoid annual changes?

WITH
indata(mth,department,job,fte) AS (
          SELECT '2023-08',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-09',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-10',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-11',1017,'Sourcing Consultant',1
UNION ALL SELECT '2023-12',1017,'Sourcing Consultant',1
UNION ALL SELECT '2024-01',1017,'Sourcing Consultant',1
UNION ALL SELECT '2024-02',1017,'Sourcing Consultant',1
UNION ALL SELECT '2024-03',1017,'Sourcing Consultant',1
)
SELECT
  department, job,  LISTAGG(mth USING PARAMETERS max_length=80, on_overflow='ERROR', separator=' ') mth,
  LISTAGG(fte USING PARAMETERS max_length=80, on_overflow='ERROR', separator=' ') fte
  from indata
  GROUP BY 1,2
  ORDER BY 1,2;
 department |         job         |                               mth                               |       fte
------------+---------------------+-----------------------------------------------------------------+-----------------
       1017 | Sourcing Consultant | 2023-08 2023-09 2023-10 2023-11 2023-12 2024-01 2024-02 2024-03 | 1 1 1 1 1 1 1 1

Hi skulkarvertica,

One thing you didn't mention is how the months range is moving.
If it's related to the current month, then you won't have to rewrite the query using this way:

create table skulkarvertica_pivot
( month        date        not null
, Department   int         not null
, Job          varchar(20) not null
, FTE          int
)
order by Department asc, month asc
segmented by hash(Department) all nodes;

 insert into skulkarvertica_pivot values
(date '2023-08-01', 1017, 'Sourcing Consultant', 1),
(date '2023-09-01', 1017, 'Sourcing Consultant', 1),
(date '2023-10-01', 1017, 'Sourcing Consultant', 1),
(date '2023-11-01', 1017, 'Sourcing Consultant', 1),
(date '2023-12-01', 1017, 'Sourcing Consultant', 1),
(date '2024-01-01', 1017, 'Sourcing Consultant', 1),
(date '2024-02-01', 1017, 'Sourcing Consultant', 1),
(date '2024-03-01', 1017, 'Sourcing Consultant', 1);

select analyze_statistics('skulkarvertica_pivot');


  select Department
       , max(Job)                                                          as Job
       , trunc(current_date, 'mm')::date                                   as referring_month
       , max(case month when add_months(referring_month, -3) then FTE end) as ref_month_minus_3
       , max(case month when add_months(referring_month, -2) then FTE end) as ref_month_minus_2
       , max(case month when add_months(referring_month, -1) then FTE end) as ref_month_minus_1
       , max(case month when add_months(referring_month,  0) then FTE end) as ref_month
       , max(case month when add_months(referring_month,  1) then FTE end) as ref_month_plus_1
       , max(case month when add_months(referring_month,  2) then FTE end) as ref_month_plus_2
       , max(case month when add_months(referring_month,  3) then FTE end) as ref_month_plus_3
       , max(case month when add_months(referring_month,  4) then FTE end) as ref_month_plus_4
       , max(case month when add_months(referring_month,  5) then FTE end) as ref_month_plus_5
       , max(case month when add_months(referring_month,  6) then FTE end) as ref_month_plus_6
    from skulkarvertica_pivot
group by Department
order by Department asc;

You won't have the proper name for the columns, but that should be usable anyway.