SQL Pivot Conditions

Question

I've written the below code to pivot a sum value by the year it occured in. Here's my results before pivot below:

Cust    Year    YTD FY      Value
---------------------------------
Aspire  2015    YTD NULL    35.00
Aspire  2015    YTD NULL    6.00
Aspire  2014    YTD FY      20.00
Aspire  2014    YTD NULL    4.00

Here's my current pivot results:

Cust    YTD     FY      2014    2015
-------------------------------------
Aspire  YTD     NULL    NULL    41.00
Aspire  YTD     FY      20.00   NULL
Aspire  YTD     NULL    4.00    NULL

The pivot works, but I want to go one step further in consolodating the columns to the below view:

Cust    2014FY  2014YTD     2015
---------------------------------
Aspire  20.00   24.00       41.00

column 1 - '2014FY'(column would be all values classified 'YTD' for the year 2014)

column 2 - '2014YTD'(column would be all values classified 'FY' for the year 2014)

SELECT  ACM.CustomerName,IG.Year AS Years, 
        CASE 
            WHEN IG.Period <= 8 THEN 'YTD'
        END AS 'YTDDataType',
        CASE 
            WHEN IG.Year = 2014 THEN 'FY'
        END AS 'FYDataType',
        IG.NetValue AS NetValue

FROM    Invoice AS IG INNER JOIN
        AccountCustomer AS ACM
            ON IG.AccountNumber = ACM.AccountNumber

WHERE   (IG.Year >= 2014)
) AS X
PIVOT
(   
    SUM(NetValue) 
    FOR Years IN ([2014], [2015])
) AS Y

I'm not having much luck - does anybody have any suggestions or ideas on a better way to do this?

Thanks in advance!

Answer 1

You can use conditional aggregation:

select ACM.CustomerName,
       sum(case when IG.Year = 2014 and FY = 'FY' then value else 0 end) as 2014_YTD_FY,
       sum(case when IG.Year = 2014 then value else 0 end) as 2014_YTD,
       sum(case when IG.Year = 2015 and FY = 'FY' then value else 0 end) as 2015_YTD_FY,
from Invoice IG join
     AccountCustomer ACM
     on IG.AccountNumber = ACM.AccountNumber
where IG.Year >= 2014
group by ACM.CustomerName and YTD = 'YTD';