Pivoting on multiple columns ang grouping by two columns

Question

I have a table that shape of data in it looks like image below and I have also included some script to easily create and populate it for experiments.

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable;
CREATE TABLE #temptable ([RiskName] VARCHAR(50)
                       , [RiskName_Change] VARCHAR(50)
                       , [PriceVal] DECIMAL(8, 2)
                       , [PriceVal_Change] DECIMAL(8, 2)
                       , [Portfolio] NVARCHAR(200)
                       , [benchmark] NVARCHAR(200)
                       , [EffectiveDate] DATE);
INSERT INTO #temptable ([RiskName], [RiskName_Change], [PriceVal], [PriceVal_Change], [Portfolio], [benchmark], [EffectiveDate])
VALUES ('OilPrice', 'OilPrice_CHANGE', 120.00, 5.50, N'PORT45', N'SP500', N'2022-06-02T00:00:00')
     , ('StocksCrash', 'StocksCrash_CHANGE', 340.00, 600.00, N'PORT45', N'SP500', N'2022-06-02T00:00:00')
     , ('SupplyChain', 'SupplyChain_CHANGE', 710.00, 23.45, N'PORT45', N'SP500', N'2022-06-02T00:00:00')

     , ('OilPrice', 'OilPrice_CHANGE', 110.00, 15.50, N'PORT45', N'SP500', N'2022-06-01T00:00:00')
     , ('StocksCrash', 'StocksCrash_CHANGE', 240.00, 500.00, N'PORT45', N'SP500', N'2022-06-01T00:00:00')
     , ('SupplyChain', 'SupplyChain_CHANGE', 400.00, 123.00, N'PORT45', N'SP500', N'2022-06-01T00:00:00')

     , ('SupplyChain', 'SupplyChain_CHANGE', 300.00, 82.40, N'PORT45', N'SP500', N'2022-05-31T00:00:00')

I think what I need is two pivots? but I am not quite sure. But the end result that I need should look like this below: Notice that each row is based on Portfolio and EffectiveDate and then if there are risks or changes to risks, those are converted to columns. Also note that in reality it could be 20 of these risks, I just added three risks for demonstration.

Portfolio | EffectiveDate | Becnhmark | OilPrice | OilPrice_Change | StocksCrash | StocksCrash_Change | SupplyChain | SupplyChain_Change |
PORT45    | 2022-06-02    | SP500     | 120.00   | 5.50            | 340.00      | 600.00             | 710.00      | 23.45              |
PORT45    | 2022-06-01    | SP500     | 110.00   | 15.50           | 240.00      | 500.00             | 400.00      | 123.00             |
PORT45    | 2022-05-31    | SP500     | NULL     | NULL            | NULL        | NLL                | 300.00      | 82.40              |

score 2 · Answer 1 · answered Jun 27 '22 at 15:47

Instead of using PIVOT which is very inflexible, you can use conditional aggregation.

It's unclear what sort of aggregation you want? SUM MIN something else?

SELECT
  t.Portfolio,
  t.benchmark,
  t.EffectiveDate,
  OilPrice           = SUM(CASE WHEN RiskName = 'OilPrice'                  THEN PriceVal END), 
  OilPrice_CHANGE    = MIN(CASE WHEN RiskName_Change = 'OilPrice'           THEN PriceVal_Change END),
  StocksCrash        = SUM(CASE WHEN RiskName = 'StocksCrash'               THEN PriceVal END), 
  StocksCrash_CHANGE = MIN(CASE WHEN RiskName_Change = 'StocksCrash_CHANGE' THEN PriceVal_Change END),
  SupplyChain        = SUM(CASE WHEN RiskName = 'SupplyChain'               THEN PriceVal END), 
  SupplyChain_CHANGE = MIN(CASE WHEN RiskName_Change = 'SupplyChain_CHANGE' THEN PriceVal_Change END)
FROM #temptable t
GROUP BY
  t.Portfolio,
  t.benchmark,
  t.EffectiveDate
ORDER BY
  t.Portfolio,
  t.benchmark,
  t.EffectiveDate DESC;

db<>fiddle

interesting. So you have for now hard coded those risks ( SupplyChain, etc ... ) . So in my final code I should do some dynamic sql because won't know what comes in that table for my risks names — UnskilledCoder, Jun 27 '22 at 16:31
See https://stackoverflow.com/a/54351133/14868997 although I recommend you move that type of code to the application level, T-SQL is not good at that kind of thing — Charlieface, Jun 27 '22 at 19:31
Unfortunately it is a SP inside a SSIS package that is going to do this. It is such a headache. Thanks I will look at that link now. — UnskilledCoder, Jun 27 '22 at 20:54

Pivoting on multiple columns ang grouping by two columns

1 Answers1