Unpivot a query in google sheets

Question

I have a query in google sheets that i use to filter table. I then need to UNPIVOT (flatten) this query output to get it in a Database style.

Query:

=query('Variables VAC'!A5:AP500,"select 
A,B,AB,AC,AD,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP where A is not null ")

gives this result

Code	Subc	30/09/2022	31/10/2022	30/11/2022	31/12/2022	31/01/2023
C0000	0000-001	844662.7140	874490.22108	905365.84788	937321.32072	970422.72499
C0000	0000-002	-106427.5020	-110185.76786	-114076.09683	-118102.48641	-122273.26335
C1003	1003-001	-399.6625	-405.66250	-411.75000	-417.92500	-424.20000
C1002	1002-001	-28380.6672	-29382.87143	-30420.29249	-31493.99638	-32606.20356
C1004	1004-001	-88911.3044	-117753.57147	-103621.88452	-137239.27133	-120781.71291
C0000	0000-003	-11414.3610	-11817.43542	-12234.67362	-12666.50433	-13113.82061
C1008	1008-001	79989.2273	81802.26833	83710.02368	85716.62069	87831.43170
C1012	1012-004	11110.0000	11221.10000	11333.31000	11446.64000	11561.11000
C1001	1001-001	134808.1692	139568.63928	144496.38932	149596.48279	154879.46691

i need to have this query "unpivoted" to get to this result:

Month	Code	Sbc	Amount	CC
30/09/2022	C0000	1007-000	844662.714	VAC
31/10/2022	C0000	1007-000	874490.2211	VAC
30/11/2022	C0000	1007-000	905365.8479	VAC
31/12/2022	C0000	1007-000	937321.3207	VAC
31/01/2023	C0000	1007-000	970422.725	VAC
30/09/2022	C0000	1007-007	-106427.502	VAC
31/10/2022	C0000	1007-007	-110185.7679	VAC
30/11/2022	C0000	1007-007	-114076.0968	VAC
31/12/2022	C0000	1007-007	-118102.4864	VAC
31/01/2023	C0000	1007-007	-122273.2633	VAC
30/09/2022	C1003	1007-008	-399.6625	VAC
31/10/2022	C1003	1007-008	-405.6625	VAC
30/11/2022	C1003	1007-008	-411.75	VAC
31/12/2022	C1003	1007-008	-417.925	VAC
31/01/2023	C1003	1007-008	-424.2	VAC
30/09/2022	C1002	1007-009	-28380.66719	VAC
31/10/2022	C1002	1007-009	-29382.87143	VAC
30/11/2022	C1002	1007-009	-30420.29249	VAC
31/12/2022	C1002	1007-009	-31493.99638	VAC
31/01/2023	C1002	1007-009	-32606.20356	VAC
30/09/2022	C1004	1007-010	-88911.30443	VAC
31/10/2022	C1004	1007-010	-117753.5715	VAC
30/11/2022	C1004	1007-010	-103621.8845	VAC
31/12/2022	C1004	1007-010	-137239.2713	VAC
31/01/2023	C1004	1007-010	-120781.7129	VAC
30/09/2022	C0000	1009-001	-11414.361	VAC
31/10/2022	C0000	1009-001	-11817.43542	VAC
30/11/2022	C0000	1009-001	-12234.67362	VAC
31/12/2022	C0000	1009-001	-12666.50433	VAC
31/01/2023	C0000	1009-001	-13113.82061	VAC
30/09/2022	C1008	1009-003	79989.22727	VAC
31/10/2022	C1008	1009-003	81802.26833	VAC
30/11/2022	C1008	1009-003	83710.02368	VAC
31/12/2022	C1008	1009-003	85716.62069	VAC
31/01/2023	C1008	1009-003	87831.4317	VAC
30/09/2022	C1012	2001-000	11110	VAC
31/10/2022	C1012	2001-000	11221.1	VAC
30/11/2022	C1012	2001-000	11333.31	VAC
31/12/2022	C1012	2001-000	11446.64	VAC
31/01/2023	C1012	2001-000	11561.11	VAC
30/09/2022	C1001	2001-005	134808.1692	VAC
31/10/2022	C1001	2001-005	139568.6393	VAC
30/11/2022	C1001	2001-005	144496.3893	VAC
31/12/2022	C1001	2001-005	149596.4828	VAC
31/01/2023	C1001	2001-005	154879.4669	VAC

I normally use the arrayformula with split and flatten but I would like to Unpivot directly from the query formula

Answer 1

try:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(C1:G1&"×"&A2:A10&"×"&B2:B10&"×"&C2:G10), "×"), 
 "select Col1,Col2,Col3,Col4,'VAC' where Col4 is not null label 'VAC'''", ))