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 :

Counterattraction

Answer 1

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

Epigenesis

Awesome! Thanks John! – Counterattraction

@Oleg happy to help – Epigenesis

There's a nice article on unpivoting by Itzik Ben-Gan that includes this technique here - itprotoday.com/sql-server/unpivoting-data In summation, this is the best technique. – Bowshot

@ChrisKoester Thanks, I agree. The CROSS APPLY allows for lateral queries and the values () allows for a bit more flexibility and control. – Epigenesis