CODEWITHSUNDEEP
sqlsql-servert-sqlunpivot
Dale Arp
Dale Arp

Reputation: 21

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

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])

Original Table and after CrossApply

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.

Needed result

Any help is appreciated.

Upvotes: 0

Views: 152

Answers (1)

SOS
SOS

Reputation: 6550

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

Upvotes: 3

Related Questions