How to do multiple PIVOT-CodePudding

I admit I am not very good at PIVOT (no pun intended) but managed to get half way to what I need but I am stuck at the second part. So here is my table and some data and also a screen shot of how the data looks right now:

CREATE TABLE #temptable ([PriceName] VARCHAR(50), [PriceName_Change] VARCHAR(50), [PriceVal] DECIMAL(28, 2), [PriceVal_Change] DECIMAL(28, 2), [Portfolio] NVARCHAR(200), [benchmark] NVARCHAR(200) , [EffectiveDate] DATE);
INSERT INTO #temptable ([PriceName], [PriceName_Change], [PriceVal], [PriceVal_Change], [Portfolio], [benchmark], [EffectiveDate])
VALUES 
 ('OilPrice', 'OilPrice_CHANGE', 1607.00, 3.61, N'PORT45', N'SP500',N'2022-06-02T00:00:00')
,('OilPrice', 'OilPrice_CHANGE', 1607.00, 3.61, N'PORT45', N'SP500',N'2022-06-01T00:00:00')
,('OilPrice', 'OilPrice_CHANGE', 607.00, 12.61, N'PORT45', N'SP500',N'2022-05-31T00:00:00')

So OilPrice is associated with values from column PriceVal and PriceName_Change is associated with values from column PriceVal_Change and that's the two things I want to PIVOT on. I managed to write it for one of them but I can't get to write it for both of them. Here is what I did:

SELECT
    portfolio
  , EffectiveDate
  , [OilPrice]
FROM
    (
        SELECT
             SM.portfolio
           , SM.PriceVal
           , SM.PriceName
           , SM.EffectiveDate
        FROM #temptable SM
    ) AS tbl
PIVOT
(
    SUM(PriceVal)
    FOR PriceName IN ([OilPrice])
) AS pvt;

Below is how I am expecting the result to show:

OilPrice | OilPrice_Change | Portfolio | Benchmark | EffectiveDate |
1607.00  | 3.61            | PORT45    | SP500     | 2022-06-02    |
1607.00  | 3.61            | PORT45    | SP500     | 2022-06-01    |
607.00   | 12.61           | PORT45    | SP500     | 2022-05-31    |

CodePudding user response：

You could do an UNPIVOT and then PIVOT that object. This is just a guess, because I don't know what you want the final output to look like, but maybe...

SELECT *
FROM
    (select PriceName, Portfolio, benchmark, 
       CONVERT(VARCHAR(20), PriceVal) AS [PriceVal],
       CONVERT(VARCHAR(20), PriceName_Change) AS [PriceName_Change]from temptable) 
as PVT
UNPIVOT
(
  Item for Items in (PriceVal, PriceName_Change)
) AS unpvt
GO

CodePudding user response：

It's unclear exactly how you want to aggregate (does it make sense to sum a Change value?). Also unclear is what result you want if there are other types of changes.

But you can use conditional aggregation like this

SELECT
  OilPrice = SUM(CASE WHEN PriceName = 'OilPrice' THEN PriceVal END), 
  OilPrice_CHANGE = SUM(CASE WHEN PriceName = 'OilPrice' THEN PriceVal_Change END),
  t.Portfolio,
  t.benchmark,
  t.EffectiveDate
FROM #temptable t
GROUP BY
  t.Portfolio,
  t.benchmark,
  t.EffectiveDate;

db<>fiddle

CodePudding user response：

Your expected results are the last 5 columns of the table, so there is no need for any pivoting.

All you have to do is filter the table so that you get only the rows with PriceName = 'OilPrice' and PriceName_Change = 'OilPrice_CHANGE' and rename the columns PriceVal to OilPrice and PriceVal_Change to OilPrice_Change:

SELECT PriceVal AS OilPrice, 
       PriceVal_Change AS OilPrice_Change, 
       Portfolio, benchmark, EffectiveDate
FROM #temptable
WHERE PriceName = 'OilPrice' AND PriceName_Change = 'OilPrice_CHANGE';

See the demo.