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

Question

The following article comes close, but I can't make the leap to my need: Unpivot pairs of associated columns to rows

IF OBJECT_ID ('dbo.tst_CrossApply') IS NOT NULL
    DROP TABLE dbo.tst_CrossApply;

create table dbo.tst_CrossApply
(
  GivenDay varchar(32) null,
  OtherData  varchar(32) null,
  CODRPL varchar(32) null,
  COD varchar (32) null,
  BODRPL varchar(32) null,
  BOD varchar (32) null,
)
go

insert into dbo.tst_CrossApply values ( 'Day1','OtherData1','<', '5','', '10')
insert into dbo.tst_CrossApply values ( 'Day2','OtherData2', '', '20','<', '30')
go

SELECT * FROM dbo.tst_CrossApply

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

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.

Answer 1

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

db<>fiddle here