SQL Server:对齐数据透视表行

Question

我有两个表，定义如下：

CREATE TABLE Portfolio.DailyStats
(Date date NOT NULL,
NAV int NOT NULL,
SP500 decimal(8,4) NULL,
R2K decimal(8,4) NULL,
NetExp decimal(8,4) NULL,
GrossExp decimal(8,4) NULL,)
GO
CREATE TABLE Portfolio.DailyPortfolio
(BbgID varchar(30) NOT NULL,
Ticker varchar(22) NULL,
Cusip char(9) NULL,
SecurityDescription varchar(50) NOT NULL,
AssetCategory varchar(25) NOT NULL,
LSPosition char(3) NULL,
Ccy varchar(25) NOT NULL,
Quantity int NULL,
AvgCost decimal(7,3) NULL,
PriceLocal decimal(7,3) NULL,
Cost int NULL,
MktValNet int NULL,
GLPeriod int NULL,
Beta decimal(4,2) NULL,
BetaExpNet int NULL,
BetaExpGross int NULL,
Delta decimal(4,2) NULL,
DeltaExpNet int NULL,
DeltaExpGross int NULL,
Issuer varchar(48) NOT NULL,
Country varchar(30) NOT NULL,
Region varchar(20) NOT NULL,
Sector varchar(30) NOT NULL,
Industry varchar(48) NOT NULL,
MktCapCategory varchar(24) NULL,
MktCapEnd int NULL,
Date date NOT NULL,
PortfolioID  AS BbgID+LSPosition+ Convert(varchar(8),Date,112) Persisted 
Primary Key)
GO

我正在尝试创建可以按年份查看每个发行人的 PNL 贡献的位置，其中每年由一列表示。盈亏贡献定义为每年的总和(GLPeriod(/该年的平均资产净值。例如，我的最终输出应如下所示：

Issuer| 2015|   2016|   2017|
:-----|-----|-------|-------|
Issuer A|   -0.012550646|   -0.012339104|   NULL|
Issuer B|   -0.00701487|    -0.000759621|   -0.004905285|
Issuer C|   NULL|   -0.002270388|   -0.003730801|

相反，电流输出如下所示：

Issuer| 2015|   2016|   2017|
:-----|-----|-------|-------|
Issuer A|   NULL|   -0.012339104|   NULL|
Issuer A|   -0.012550646|   NULL|   NULL|
Issuer B|   NULL|   NULL|   -0.004905285|
Issuer B|   NULL|   -0.000759621|   NULL|
Issuer B|   -0.00701487|    NULL|   NULL|
Issuer C|   NULL|   NULL|   -0.003730801|
Issuer C|   NULL|   -0.002270388|   NULL|

这是我使用的代码：

CREATE VIEW Portfolio.ContributionByYear
AS
WITH a
AS
(
SELECT Issuer, SUM(GLPeriod) PNL, DATEPART(yyyy, Date) Year
FROM Portfolio.DailyPortfolio
GROUP BY Issuer, DATEPART(yyyy, Date)
)
,
b AS
(
SELECT AVG(CAST(NAV AS BIGINT)) AvgNAV, DATEPART(yyyy, Date) Year
FROM Portfolio.DailyStats
GROUP BY DATEPART(yyyy, Date)
),
c AS
(
SELECT a.Issuer, a.PNL, a.Year, b.AvgNAV , CONVERT( decimal (15,3), 
a.PNL)/CONVERT( decimal (15,3), b.AvgNAV) [Contrib]
FROM a
JOIN b
ON a.Year = b.Year
)
SELECT Issuer, [2015],[2016],[2017]
FROM c
PIVOT
(
SUM(c.Contrib)
FOR [Year] IN ([2015],[2016],[2017])
) AS PivotTable
GO

我对这段代码的另一个问题是我在数据透视表中使用了 SUM 聚合函数，但它实际上没有添加任何内容。它只是将数据从行翻转到列。但是，我似乎无法在不使用某种聚合函数的情况下透视数据。

Answer 1

尝试从cCTE 中删除a.PNL和b.AvgNAV。

SELECT a.Issuer, a.Year, --here `a.PNL` and `b.AvgNAV` is removed 
CONVERT( decimal (15,3), a.PNL)/CONVERT( decimal (15,3), b.AvgNAV) [Contrib]
FROM a
JOIN b
ON a.Year = b.Year

开始给Cte's起正确的名称，使代码更具可读性

CREATE VIEW Portfolio.ContributionByYear
AS
WITH cteDailyPortfolio
AS
(
SELECT Issuer, SUM(GLPeriod) PNL, DATEPART(yyyy, Date) Year
FROM Portfolio.DailyPortfolio
GROUP BY Issuer, DATEPART(yyyy, Date)
),
cteDailyStats AS
(
SELECT AVG(CAST(NAV AS BIGINT)) AvgNAV, DATEPART(yyyy, Date) Year
FROM Portfolio.DailyStats
GROUP BY DATEPART(yyyy, Date)
),
pivotData AS
(
SELECT a.Issuer, a.Year, --here `a.PNL` and `b.AvgNAV` is removed 
CONVERT( decimal (15,3), a.PNL)/CONVERT( decimal (15,3), b.AvgNAV) [Contrib]
FROM cteDailyPortfolio a
JOIN cteDailyStats b
ON a.Year = b.Year
)
SELECT Issuer, [2015],[2016],[2017]
FROM pivotData 
PIVOT
(
SUM(c.Contrib)
FOR [Year] IN ([2015],[2016],[2017])
) AS PivotTable
GO