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.

As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Mar 01 '22 at 22:03
Welcome! You can also use tools like https://dbfiddle.uk/ which is not only great for sharing, but it also provides a *markdown* option that creates nicely formatted tables ready for pasting in html. Here's an example based on what you posted. https://dbfiddle.uk/markdown?rdbms=sqlserver_2019&fiddle=025dd9f89baa9a7ee4dc1291727e540b — SOS, Mar 01 '22 at 22:14

SOS · Accepted Answer · 2022-03-02T18:43:57.923

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

Thank you Dale K. I understand the code you provided and how it works. It works well for my example, and I will attempt to adapt it to a much more complex need for migration to a new system and have confidence it will work for me. I welcome your suggestion and other solution considerations, as well as a reminder that dbfiddle is a tool I can use. You have saved me a lot of frustration time. I consider this answered. — Dale Arp, Mar 02 '22 at 14:04

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

1 Answers1