How to unpivot columns using CROSS APPLY in SQL Server 2012

Question

I want to use CROSS APPLY to UNPIVOT multiple columns.

The columns CGL, CPL, EO should become Coverage Type, the values for CGL, CPL, EO should go in column Premium, and values for CGLTria,CPLTria,EOTria should go in column Tria Premium

declare @TestDate table  ( 
                            QuoteGUID varchar(8000), 
                            CGL money, 
                            CGLTria money, 
                            CPL money,
                            CPLTria money,
                            EO money,
                            EOTria money
                            )

INSERT INTO @TestDate (QuoteGUID, CGL, CGLTria, CPL, CPLTria, EO, EOTria)
VALUES ('2D62B895-92B7-4A76-86AF-00138C5C8540', 2000, 160, 674, 54, 341, 0),
       ('BE7F9483-174F-4238-8931-00D09F99F398', 0, 0, 3238, 259, 0, 0),
       ('BECFB9D8-D668-4C06-9971-0108A15E1EC2', 0, 0, 0, 0, 0, 0)

SELECT * FROM @TestDate

Output:

The result should be like that :

score 15 · Accepted Answer · answered Feb 07 '18 at 01:04

15

One quick and easy way is with VALUES

Example

select A.QuoteGUID
      ,B.*
 From  @TestDate A
 Cross Apply ( values ('CGL',CGL,CGLTria)
                     ,('CPL',CPL,CPLTria)
                     ,('EO',EO,EOTria)
             ) B (CoverageType,Premium,TiraPremium)

Returns

answered Feb 07 '18 at 01:04

John Cappelletti

79,615
7
44
66

Awesome! Thanks John! – Serdia Feb 07 '18 at 03:40
@Oleg happy to help – John Cappelletti Feb 07 '18 at 03:57
There's a nice article on unpivoting by Itzik Ben-Gan that includes this technique here - https://www.itprotoday.com/sql-server/unpivoting-data In summation, this is the best technique. – Chris Koester Feb 07 '20 at 16:24
@ChrisKoester Thanks, I agree. The CROSS APPLY allows for lateral queries and the values () allows for a bit more flexibility and control. – John Cappelletti Feb 07 '20 at 16:32

How to unpivot columns using CROSS APPLY in SQL Server 2012

1 Answers1

Linked