By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table sample ( sku nvarchar(max), data_type nvarchar(max),
value_type nvarchar(max), value float )
Insert Into sample ( sku, data_type, value_type, value )
VALUES ( 'sku1', 'forecast', 'units', '100' ),
( 'sku1', 'forecast', 'gross sales', '40' ),
( 'sku1', 'forecast', 'net sales', '50' ),
( 'sku1', 'forecast', 'gross profit', '60' ),
( 'sku1', 'actual', 'units', '200' ),
( 'sku1', 'actual', 'gross sales', '80' ),
( 'sku1', 'actual', 'net sales', '30' ),
( 'sku1', 'actual', 'gross profit', '20' );
8 rows affected
Select a.sku,
Max( Case When a.value_type = 'units' Then a.value - f.value End ) as ACvsFC_units,
Max( Case When a.value_type = 'gross sales' Then a.value - f.value End ) as ACvsFC_gross_sales,
Max( Case When a.value_type = 'net sales' Then a.value - f.value End ) as ACvsFC_net_sales,
Max( Case When a.value_type = 'gross profit' Then a.value - f.value End ) as ACvsFC_gross_profit,
--
Max( Case When f.data_type = 'forecast' And f.value_type = 'units'
Then f.value
End ) as FC_units,
Max( Case When f.data_type = 'forecast' And f.value_type = 'gross sales'
Then f.value
End ) as FC_gross_sales,
Max( Case When f.data_type = 'forecast' And f.value_type = 'net sales'
Then f.value
End ) as FC_net_sales,
Max( Case When f.data_type = 'forecast' And f.value_type = 'gross profit'
Then f.value
End ) as FC_gross_profit,
--
Max( Case When a.data_type = 'actual' And a.value_type = 'units'
Then a.value
End ) as AC_units,
Max( Case When a.data_type = 'actual' And a.value_type = 'gross sales'
Then a.value
End ) as AC_gross_sales,
Max( Case When a.data_type = 'actual' And a.value_type = 'net sales'
Then a.value
End ) as AC_net_sales,
Max( Case When a.data_type = 'actual' And a.value_type = 'gross profit'
Then a.value
End ) as AC_gross_profit
From sample a
Inner Join sample f ON( f.sku = a.sku And f.value_type = a.value_type )
Where f.data_type = 'forecast' And
a.data_type = 'actual'
Group By a.sku
| sku | ACvsFC_units | ACvsFC_gross_sales | ACvsFC_net_sales | ACvsFC_gross_profit | FC_units | FC_gross_sales | FC_net_sales | FC_gross_profit | AC_units | AC_gross_sales | AC_net_sales | AC_gross_profit |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| sku1 | 100 | 40 | -20 | -40 | 100 | 40 | 50 | 60 | 200 | 80 | 30 | 20 |
Warning: Null value is eliminated by an aggregate or other SET operation.