Michael Michael - 22 days ago 8
SQL Question

SQL Pivot with Union All

I'm using SQL Server 2012. I have 2 pivots (Current Year (CY) vs Last Year (LY) that work fine by themselves but want to Union them and have the results all togeather, how do I go about this? I need to have the data look like like this:

Cost Centre Name May_CY May_LY Jun_CY Jun LY.....This_Yr_Total Last_Yr_Total


This is my code:

SELECT
pvt.*,
Isnull(pvt.jan_CY, 0) +
Isnull(pvt.feb_CY, 0) +
Isnull(pvt.mar_CY, 0) +
Isnull(pvt.apr_CY, 0) +
Isnull(pvt.may_CY, 0) +
Isnull(pvt.jun_CY, 0) +
Isnull(pvt.jul_CY, 0) +
Isnull(pvt.aug_CY, 0) +
Isnull(pvt.sept_CY, 0) +
Isnull(pvt.oct_CY, 0) +
Isnull(pvt.nov_CY, 0) +
Isnull(pvt.Dec_CY, 0)
AS This_Year
FROM (SELECT [CostCentre], [CostCentreDesc] AS Name, { fn CONCAT(DATENAME(month, dbo.tblGLS215_2016_2017.AccDate), '_CY') } AS AccMonth, [RecordedAmount]
FROM [tblGLS215_2016_2017]
WHERE Employee <> '') AS s PIVOT (SUM([RecordedAmount]) FOR [AccMonth] IN (May_CY, Jun_CY, Jul_CY, Aug_CY, Sept_CY, Oct_CY, Nov_CY, Dec_CY, Jan_CY, Feb_CY, Mar_CY, Apr_CY)) AS pvt
UNION ALL
SELECT
pvt.*,
Isnull(pvt.jan_LY, 0) +
Isnull(pvt.feb_LY, 0) +
Isnull(pvt.mar_LY, 0) +
Isnull(pvt.apr_LY, 0) +
Isnull(pvt.may_LY, 0) +
Isnull(pvt.jun_LY, 0) +
Isnull(pvt.jul_LY, 0) +
Isnull(pvt.aug_LY, 0) +
Isnull(pvt.sept_LY, 0) +
Isnull(pvt.oct_LY, 0) +
Isnull(pvt.nov_LY, 0) +
Isnull(pvt.Dec_LY, 0)
AS Last_Year
FROM (SELECT [CostCentre], [CostCentreDesc] AS Name, { fn CONCAT(DATENAME(month, dbo.tblGLS215_2015_2016.AccDate), '_LY') } AS AccMonth, [RecordedAmount]
FROM [tblGLS215_2015_2016]
WHERE Employee <> '') AS s PIVOT (SUM([RecordedAmount]) FOR [AccMonth] IN (May_LY, Jun_LY, Jul_LY, Aug_LY, Sept_LY, Oct_LY, Nov_LY, Dec_LY, Jan_LY, Feb_LY, Mar_LY, Apr_LY)) AS pvt


Any help would be greatly appreciated.

Answer
Select A.[CostCentre], A.Name 
      ,A.[May_CY],B.[May_LY]
      ,A.[Jun_CY],B.[Jun_LY]
      ,A.[Jul_CY],B.[Jul_LY]
      ,A.[Aug_CY],B.[Aug_LY]
      ,A.[Sept_CY],B.[Sept_LY]
      ,A.[Oct_CY],B.[Oct_LY]
      ,A.[Nov_CY],B.[Nov_LY]
      ,A.[Dec_CY],B.[Dec_LY]
      ,A.[Jan_CY],B.[Jan_LY]
      ,A.[Feb_CY],B.[Feb_LY]
      ,A.[Mar_CY],B.[Mar_LY]
      ,A.[Apr_CY],B.[Apr_LY]
      ,A.[This_Year] as This_Yr_Total , B.[Last_Year] as  Last_Yr_Total

From
(
SELECT        
pvt.*, 
Isnull(pvt.jan_CY, 0) + 
Isnull(pvt.feb_CY, 0) + 
Isnull(pvt.mar_CY, 0) + 
Isnull(pvt.apr_CY, 0) + 
Isnull(pvt.may_CY, 0) + 
Isnull(pvt.jun_CY, 0) + 
Isnull(pvt.jul_CY, 0) + 
Isnull(pvt.aug_CY, 0) + 
Isnull(pvt.sept_CY, 0) + 
Isnull(pvt.oct_CY, 0) + 
Isnull(pvt.nov_CY, 0) +
Isnull(pvt.Dec_CY, 0)
AS  This_Year
FROM            (SELECT        [CostCentre], [CostCentreDesc] AS Name, { fn CONCAT(DATENAME(month, dbo.tblGLS215_2016_2017.AccDate), '_CY') } AS AccMonth, [RecordedAmount]
                          FROM            [tblGLS215_2016_2017]
                          WHERE        Employee <> '') AS s PIVOT (SUM([RecordedAmount]) FOR [AccMonth] IN (May_CY, Jun_CY, Jul_CY, Aug_CY, Sept_CY, Oct_CY, Nov_CY, Dec_CY, Jan_CY, Feb_CY, Mar_CY, Apr_CY)) AS pvt
)as A
Inner Join
(
SELECT        
pvt.*, 
Isnull(pvt.jan_LY, 0) + 
Isnull(pvt.feb_LY, 0) + 
Isnull(pvt.mar_LY, 0) + 
Isnull(pvt.apr_LY, 0) + 
Isnull(pvt.may_LY, 0) + 
Isnull(pvt.jun_LY, 0) + 
Isnull(pvt.jul_LY, 0) + 
Isnull(pvt.aug_LY, 0) + 
Isnull(pvt.sept_LY, 0) + 
Isnull(pvt.oct_LY, 0) + 
Isnull(pvt.nov_LY, 0) +
Isnull(pvt.Dec_LY, 0)
AS  Last_Year
FROM            (SELECT        [CostCentre], [CostCentreDesc] AS Name, { fn CONCAT(DATENAME(month, dbo.tblGLS215_2015_2016.AccDate), '_LY') } AS AccMonth, [RecordedAmount]
                   FROM            [tblGLS215_2015_2016]
WHERE        Employee <> '') AS s PIVOT (SUM([RecordedAmount]) FOR [AccMonth] IN (May_LY, Jun_LY, Jul_LY, Aug_LY, Sept_LY, Oct_LY, Nov_LY, Dec_LY, Jan_LY, Feb_LY, Mar_LY, Apr_LY)) AS pvt
) as B
On A.[CostCentre]=B.[CostCentre] and A.Name=B.Name
Comments