Pivot inside Select Query (1 Viewer)

[atzdgreat]

hi everyone. is it possible to union transform query?

SELECT * FROM (
TRANSFORM Sum(t0.TotCost) AS SumOfTotCost
SELECT t3.HierarchyLine+'.02' AS Hierarchy, t3.HierarchyMain, t3.HierarchyDetails, t0.LedgerDet, t1.ThisPeriod, t2.YTD
FROM (([Main Table] AS t0 LEFT JOIN (SELECT LedgerAcct, SUM(TotCost) AS ThisPeriod FROM [Main Table] WHERE FORMAT(Period,'MMMM yyyy') = 'January 2024' GROUP BY LedgerAcct)  AS t1 ON t0.LedgerAcct = t1.LedgerAcct) LEFT JOIN (SELECT LedgerAcct, SUM(TotCost) AS YTD FROM [Main Table] WHERE Period BETWEEN #01/01/24# AND #02/29/24# GROUP BY LedgerAcct)  AS t2 ON t0.LedgerAcct = t2.LedgerAcct) LEFT JOIN [Main Hierarchy] AS t3 ON MID(t0.LedgerAcct,1,4) = t3.HierarchyStartNum
WHERE FORMAT(t0.Period,'MMMM yyyy') = 'February 2024' AND t3.HierarchyLine IS NOT NULL
GROUP BY t3.HierarchyLine, t3.HierarchyMain, t3.HierarchyDetails, t0.LedgerDet, t1.ThisPeriod, t2.YTD
PIVOT t0.CostCenter
UNION ALL
TRANSFORM Sum(t0.TotCost) AS SumOfTotCost
SELECT t3.HierarchyLine+'.01' AS Hierarchy, t3.HierarchyMain, t3.HierarchyDetails, t0.LedgerDet, t1.ThisPeriod, t2.YTD
FROM (([Main Table] AS t0 LEFT JOIN (SELECT LedgerAcct, SUM(TotCost) AS ThisPeriod FROM [Main Table] WHERE FORMAT(Period,'MMMM yyyy') = 'January 2024' GROUP BY LedgerAcct)  AS t1 ON t0.LedgerAcct = t1.LedgerAcct) LEFT JOIN (SELECT LedgerAcct, SUM(TotCost) AS YTD FROM [Main Table] WHERE Period BETWEEN #01/01/24# AND #02/29/24# GROUP BY LedgerAcct)  AS t2 ON t0.LedgerAcct = t2.LedgerAcct) LEFT JOIN [Main Hierarchy] AS t3 ON MID(t0.LedgerAcct,1,4) = t3.HierarchyStartNum
WHERE FORMAT(t0.Period,'MMMM yyyy') = 'February 2024' AND t3.HierarchyLine IS NOT NULL
GROUP BY t3.HierarchyLine, t3.HierarchyMain, t3.HierarchyDetails, t0.LedgerDet, t1.ThisPeriod, t2.YTD
PIVOT t0.CostCenter
) x;

 

[arnelgp]

maybe Union it first (save the query).
then Transform the resulting query.