How to divide results into separate rows based on year?

Question

I have a query that looks at profits and operations costs of different stores based on the fiscal year, and currently the fiscal years and variables are sorted into single, respective columns such as:

FiscalYear    Metric    Store    Amount
2017          Profit    A        220
2017          Cost      A        180
2018          Profit    B        200 
2018          Cost      B        300
...

I need to cross tab the rows so that for each store, I can compare the 2017 profit against the 2018 profit, and 2017 cost against the 2018 cost.

I broke out profits and costs by creating CASE WHEN statements for the ProfitLossTable, but I don't know how to make it create a "2017 Profit" and "2018 Profit" column, respectively, for each Store.

WITH [Profits, Cost] AS 
(
SELECT ID, StoreID, Number, FYYearID,
CASE WHEN ID = 333 then Number END AS Profit
CASE WHEN ID = 555 then Number END AS Cost
FROM ProfitLossTable
),
Location AS
(
Select StoreID, StoreName
FROM StoreTable
),
FiscalMonth AS
(
SELECT FYYearID, FYYear
FROM FiscalMonthTable
)
SELECT A.Profit, A.Cost
FROM [Profits, Cost] A
JOIN Location B
ON A.StoreID = B.StoreID
JOIN FiscalMonth C
ON A.FYYearID = C.FYYearID

The code above shows this, and I feel like I am close to creating columns based on year, but I don't know what to do next.

FiscalYear    Store    Profit    Cost
2017          A        220       100
2017          A        180       100
2018          B        200       100 
2018          B        300       100

PIVOT is your friend here See https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Duncan Howe, Jan 09 '19 at 17:05

score 0 · Accepted Answer · answered Jan 09 '19 at 17:08

As a working (on my machine anyway ;-p) example using your data:

create table #temp(
FiscalYear int not null,
Metric nvarchar(50) not null,
Store nvarchar(10) not null,
Amount int not null
)

insert into #temp
values
(2017, N'Profit', N'A', 220),
(2017, N'Cost', N'A', 180),
(2018, N'Profit', N'B', 200),
(2018, N'Cost', N'B', 300)

select * from #temp

select Metric,
[2017] as [2017],
[2018] as [2018]
from (select FiscalYear, Amount, Metric from #temp) base_data
PIVOT
(SUM(Amount) FOR FiscalYear in ([2017], [2018])
) as pvt
order by pvt.Metric

drop table #temp

How to divide results into separate rows based on year?

1 Answers1