I need to Pivot date/month rows to column.
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
Best Answer
-
marcothesane
- Select Field - Administrator
If you want the stored procedure, shout. I'll try to paste its source here.
0
Answers
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 | 1Hi 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 1Hi 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.