本文介绍了如何在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)
返回
这篇关于如何在SQL Server 2012中使用CROSS APPLY取消透视列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!