如何在SQL Server 2012中使用CROSS APPLY取消透视列 [英] How to unpivot columns using CROSS APPLY in SQL Server 2012

问题描述

我想使用CROSS APPLY来UNPIVOT多列.

I want to use CROSS APPLY to UNPIVOT multiple columns.

CGL, CPL, EO列应变为Coverage Type，CGL, CPL, EO的值应进入Premium列，而CGLTria,CPLTria,EOTria的值应进入Tria Premium列

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

输出:

结果应该是这样的:

推荐答案

一种简单快捷的方法是VALUES

One quick and easy way is with VALUES

示例

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

返回

这篇关于如何在SQL Server 2012中使用CROSS APPLY取消透视列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！