I need to unpivot columns to rows where pairs of columns stay together in the results-CodePudding

The following article comes close, but I can't make the leap to my need:

The above script returns the above with the second piture minus the needed Column 'Parameter'.
I can get this column, but not the pairing of the RPL and Result columns using UNPIVOT In my database there are several 'OtherData' columns, and several pairs of columns to CrossApply and/or UNPIVOT.

The following includes the Parameter column I need, which is one of the second of the paried column headings.

Any help is appreciated.

CodePudding user response：

You're close. See the "Unpivoting" example linked in the next thread.

SELECT t.[GivenDay]
       , t.[OtherData]
       , v.[Param]
       , v.[RPL]
       , v.[Result]
FROM    [dbo].[tst_CrossApply] t
CROSS APPLY (
      VALUES ('COD', [CODRPL], [COD])
           , ('BOD', [BODRPL], [BOD])
    ) v ([Param], [RPL],[Result])

Update 2022-03-02

I'm not aware of a simple alternative using UNPIVOT. The closest I could get was more convoluted than just using CROSS APPLY

SELECT cod.GivenDay, cod.OtherData, cod.Param, cod.RPL, cod.Result
FROM   (
          SELECT  GivenDay, OtherData, COD, CODRPL AS RPL
          FROM    [dbo].[tst_CrossApply] t
       ) pvt
       UNPIVOT  
       (
          Result FOR Param IN (COD)  
       ) AS cod
UNION ALL 
SELECT bod.GivenDay, bod.OtherData, bod.Param, bod.RPL, bod.Result
FROM   (
          SELECT  GivenDay, OtherData, BOD, BODRPL AS RPL
          FROM    [dbo].[tst_CrossApply] t
       ) pvt
       UNPIVOT  
       (
          Result FOR Param IN (BOD)  
       ) AS bod
ORDER BY GivenDay, OtherData, Param