Hi All,
i have sample data
Year Period Companyid ProductBrand Product ProductType ActualCost netprofit
2019 004 P1010 R1010 M1010 P1010 345 450
2020 004 P1010 R1010 M1010 P1010 234 300
2021 004 P1010 R1010 M1010 P1010 100 98
2019 003 P1013 R1013 M1013 P1013 123 432
2020 004 P1012 R1012 M1012 P1012 421 234
here ActualCost and netprofit is measure.
and requirement is dynamically pivot , but we are only do static pivot, please see below code
SELECT * into #temp FROM
(
SELECT
Concat([Year],'-','ActualCost') as [YearActualCost],
Concat([Year],'-','netprofit') as [Yearnetprofit],
Concat([Year],'-', [Period],'-','ActualCost') as [Date],
Concat([Year],'-', [Period],'-','netprofit') as [netprofitDate],
--Concat([Year],'-', [Period]) as [netprofitDate],
Companyid,
Prdoduct,
ActualCost,
ActualCost as MonthActualCost,
netprofit,
netprofit as Monthnetprofit
FROM [StageDatabase].[dbo].[Cost]
) t
PIVOT(Sum([ActualCost]) for [YearActualCost] in([2019-ActualCost], [2020-ActualCost], [2021-ActualCost])) AS pivot_table0
PIVOT(Sum([netprofit]) for [Yearnetprofit] in([2019-netprofit], [2020-netprofit], [2021-netprofit])) AS pivot_table1
PIVOT(Sum([MonthActualCost]) for [Date] in([2019-003-ActualCost], [2020-003-ActualCost], [2021-003-ActualCost], [2019-004-ActualCost], [2020-004-ActualCost], [2021-004-ActualCost])) AS pivot_table2
PIVOT(Sum([Monthnetprofit]) for [netprofitDate] in([2019-003-netprofit], [2020-003-netprofit], [2021-003-netprofit], [2019-004-netprofit], [2020-004-netprofit], [2021-004-netprofit])) AS pivot_table3;
select Companyid,Product,
isnull([2019-ActualCost],0) as [2019-ActualCost],
isnull([2020-ActualCost],0) as [2020-ActualCost] ,
isnull ([2021-ActualCost],0) as [2021-ActualCost],
isnull([2019-netprofit],0) as [2019-netprofit],
isnull([2020-netprofit],0) as [2020-netprofit],
isnull([2021-netprofit],0) as [2021-netprofit],
isnull([2019-003-ActualCost],0) as [2019-003-ActualCost],
isnull([2020-003-ActualCost],0) as [2020-003-ActualCost],
isnull([2021-003-ActualCost],0) as [2021-003-ActualCost],
isnull([2019-004-ActualCost],0) as [2019-004-ActualCost],
isnull([2020-004-ActualCost], 0) as [2020-004-ActualCost],
isnull([2021-004-ActualCost],0) as [2021-004-ActualCost],
isnull([2019-003-netprofit],0) as [2019-003-netprofit],
isnull([2020-003-netprofit],0) as [2020-003-netprofit],
isnull([2021-003-netprofit],0) as [2020-003-netprofit] ,
isnull([2019-004-netprofit],0) as [2019-004-netprofit],
isnull([2020-004-netprofit],0) as [2019-004-netprofit],
isnull([2021-004-netprofit],0) as [2019-004-netprofit]
from #temp
output