如何在 SQL Server 2012 中使用 CROSS APPLY 取消透视列

2022-01-03 00:00:00 tsql sql-server sql-server-2012 cross-apply

我想将 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)

退货

相关文章