sql - 关于 SQL Pivot
问题描述
嗨,我一直在尝试对以下数据进行 PIVOT。
LeaseId | ColumnHeader | UIPayments | UIResidual
-------------------------------------------------
25573 | 2019-05-01 | 0.0000 | 0.0000
25573 | 2019-06-01 | 0.0000 | 0.0000
25573 | 2019-07-01 | 0.0000 | 0.0000
25573 | 2019-08-01 | 0.0000 | 0.0000
25573 | 2019-09-01 | 0.0000 | 0.0000
25573 | 2019-10-01 | 0.0000 | 0.0000
结果数据集应如下所示:
LeaseId |Details | 2019-05-01| 2019-06-01|2019-07-01 |2019-08-01
-----------------------------------------------------------------
25573 |UIPayments |5.0000 |5.0000 |5.0000 |5.0000
25573 |UIResidual |1.0000 |1.0000 |1.0000 |1.0000
可能吗?
提前致谢。
到目前为止我尝试过的查询:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',[' + CAST(DATEFROMPARTS(#TempMonths.y, #TempMonths.m, '01') AS VARCHAR(20)) + ']'
from #TempMonths
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT LeaseID, UIPayments, UIResidual, PVResidual, PVPayments, NetInvestment,' + @cols + ' from
(
SELECT * FROM #TmpDetails
) x
pivot
(
UIPayments, UIResidual, PVResidual, PVPayments, NetInvestment
for ColumnHeader in (' + @cols + ')
) p '
我真的不知道这是否是一个有效的查询。
提前致谢!
解决方案
条件聚合应该适用于几乎任何数据库:
select LeaseId, 'UIPayments' as details,
sum(case when ColumnHeader = '2019-05-01' then UIPayments else 0 end) as val_20190501,
sum(case when ColumnHeader = '2019-06-01' then UIPayments else 0 end) as val_20190601,
sum(case when ColumnHeader = '2019-07-01' then UIPayments else 0 end) as val_20190701,
sum(case when ColumnHeader = '2019-08-01' then UIPayments else 0 end) as val_20190801
from t
group by LeaseId
union all
select LeaseId, 'UIResidual' as details,
sum(case when ColumnHeader = '2019-05-01' then UIResidual else 0 end) as val_20190501,
sum(case when ColumnHeader = '2019-06-01' then UIResidual else 0 end) as val_20190601,
sum(case when ColumnHeader = '2019-07-01' then UIResidual else 0 end) as val_20190701,
sum(case when ColumnHeader = '2019-08-01' then UIResidual else 0 end) as val_20190801
from t
group by LeaseId;
我想诀窍是您需要分别处理每一列。在许多数据库中,您可以使用横向连接而不是union all
.
推荐阅读
- ruby - ruby 对象如何成为对象的子类?
- javascript - API 端点 URL
- scope - I2C 在 LPC824 和 MMA8453Q 之间不起作用
- postgresql - 如何在 postgresql 中禁用检查约束
- c++ - 如何将带有矢量或其他标准库容器的对象保存到 C++ 中的二进制文件?
- prometheus - Prometheus 目标显示 kubelet 的 403
- php - Instamojo 支付网关
- java - 如何从自定义 xml 文件访问文本视图?
- r - r:在函数内的公式中使用傅立叶时未找到对象
- android - 从查询中返回 kotlin 元组