如何使用UNPIVOT来规范化输出SQL Server 2012
有没有可能像这样对数据进行分析？
列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 回答 4
Stack Overflow用户
发布于 2018-02-07 01:38:09
下面的查询应该可以工作。
`Select * From (select top 3 QuoteGUID，CoverageType，Premium from Align_EnvionmentalRating_PremiumHistory unpivot ( Premium for CoverageType in ( CGL，CPL，EO) as up )A
内连接
(从Align_EnvionmentalRating_PremiumHistory unpivot ( TriaPremium for CoverageType in ( CGLTria，CPLTria，EOTria) )中选择前3个QuoteGUID、CoverageType、Premium ) as up ) as B
在A.QuoteGUID=B.QuoteGUID和A.CoverageType=substring上(B.CoverageType，1，Len(B.CoverageType)-4)`
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 <> 0Stack Overflow用户
发布于 2018-02-07 05:35:12
这将从所提供的示例数据中生成您想要的确切输出。它可能会变得有点糟糕，因为有多达60列需要规范化，但编写它应该是一次性的事情。在做了一些工作之后，您似乎真的需要将它分成至少两个表，但这是一整罐蠕虫。
with NormalizedData
(
PolicyNumber
, CoverageType
, Premium
, TriaPremium
) as
(
SELECT PolicyNumber
, 'CGL'
, CGL
, CGLTria
FROM TestDate
UNION ALL
SELECT PolicyNumber
, 'CPL'
, CPL
, CPLTria
FROM TestDate
UNION ALL
SELECT PolicyNumber
, 'EO'
, EO
, EOTria
FROM TestDate
)
select td.PolicyNumber
, td.PolicyEffectiveDate
, td.AnnualStatementLine
, nd.CoverageType
, nd.Premium
, nd.TriaPremium
, td.RiskState
from TestDate td
join NormalizedData nd on nd.PolicyNumber = td.PolicyNumber
order by td.PolicyNumber
, nd.CoverageTypehttps://stackoverflow.com/questions/48648422
复制相似问题
腾讯云开发者
