如何使用UNPIVOT来规范化输出SQL Server 2012
如何使用UNPIVOT来规范化输出SQL Server 2012
提问于 2018-02-07 01:12:43
有没有可能像这样对数据进行分析？
列cg1、cp1、EO应变为Coverage Type，cg1、cp1、EO的值应位于列Premium中，而CGLTria、CPLTria、EOTria的值应位于列Tria Premium中
另外，如果CGL、CPL和EO的值为0，那么我不需要这些列。
我可以执行简单的UNPIVOT，但当需要引入更多列时，我会感到困惑
SELECT TOP 3
QuoteGUID, CoverageType, Premium
FROM
Align_EnvionmentalRating_PremiumHistory
UNPIVOT
(Premium FOR CoverageType IN (CGL, CPL, EO)) AS up
更新:添加更多数据
列：Policy Number, Policy Effective Date, Annual Statement Line, Fees, Risk State应该保持不变。列：CGL, CGLTria,CPL,CPLTria,EO,EOTria应为UNPIVOTED
select top 3 [Policy Number],
[Policy Effective Date],
'17.1' as [Annual Statement Line],
CGL,
CGLTria,
CPL,
CPLTria,
EO,
EOTria,
Fees,
[Risk State]
from #Test
更新:添加消耗品数据：
create table dbo.TestDate (
PolicyNumber varchar(50),
PolicyEffectiveDate datetime,
AnnualStatementLine decimal(5,1),
CGL money,
CGLTria money,
CPL money,
CPLTria money,
EO money,
EOTria money,
Fees money,
RiskState varchar(2)
)
INSERT INTO dbo.TestDate (PolicyNumber, PolicyEffectiveDate , AnnualStatementLine, CGL , CGLTria , CPL ,CPLTria ,EO ,EOTria ,Fees ,RiskState )
values ('ENV560000001-00','2018-01-11 23:21:00',17.1,2000,160,674,54,341,0,250,'TX'),
('ENV560000002-00','2018-01-11 00:56:00',17.1,0,0,3238,259,0,0,250,'NV'),
('ENV560000003-00','2018-01-12 01:10:00',17.1,0,0,6045,484,0,0,250,'ND'),
('ENV560000004-00','2018-01-14 01:18:00',17.1,0,0,0,0,0,0,0,'ND')
select * from dbo.TestDate Stack Overflow用户
发布于 2018-02-07 01:43:56
我不确定这是否回答了您的问题，但您可以使用以下简单的UNION查询来完成此操作：
SELECT
ID = a1.ID,
CoverageType = 'CGL',
Premium = a1.CGL,
TriaPremium = a1.CGLTria
FROM
Align_EnvionmentalRating_PremiumHistory AS a1
WHERE
a1.CGL <> 0
UNION ALL
SELECT
ID = a2.ID,
CoverageType = 'CPL',
Premium = a2.CPL,
TriaPremium = a2.CPLTria
FROM
Align_EnvionmentalRating_PremiumHistory AS a2
WHERE
a2.CPL <> 0
UNION ALL
SELECT
ID = a3.ID,
CoverageType = 'EO',
Premium = a3.EO,
TriaPremium = a3.EOTria
FROM
Align_EnvionmentalRating_PremiumHistory AS a3
WHERE
a3.EO <> 0页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48648422
复制相关文章
相似问题
腾讯云开发者
