I need to Pivot date/month rows to column.

skulkarvertica

I have a set of month rows. The number of rows will vary and is not fixed(could be up to 60 months i.e. 5+ years). Need to convert them into columns.
FROM THIS
Month Department Job FTE
AUG 2023 1017 Sourcing Consultant. 1
SEP 2023 1017 Sourcing Consultant. 1
OCT 2023 1017 Sourcing Consultant. 1
NOV 2023 1017 Sourcing Consultant. 1
DEC 2023 1017 Sourcing Consultant. 1
JAN 2024 1017 Sourcing Consultant. 1
FEB 2024 1017 Sourcing Consultant. 1
MAR 2024 1017 Sourcing Consultant. 1

_TO THIS _
AUG 2023 SEP 2023 OCT 2023 NOV 2023 DEC 2023 JAN 2024 FEB 2024 MAR 2024
1017 Sourcing Consultant. 1 1 1 1 1 1 1 1

marcothesane

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

marcothesane

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

skulkarvertica

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!!!

mosheg

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

VValdar

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.