sql-server - 替换动态数据透视 sql 查询中的空值
问题描述
图像转录:
FTE/RATE CARD | 2018-01-01 | 2018-02-01 | 2018-03-01 | 2018-04-01 | 2018-05-01 | 2018-06-01 | ...
-------------------------------------------------------------------------------------------------
FTE 3 | NULL | NULL | NULL | 33 | 38 | 40.5 | ...
我有一个如下所示的数据透视 sql 查询。我需要在结果中用零替换 NULL 值。我不知道在查询中究竟在哪里使用 IsNull 或 Coalesce 函数。
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @cols = STUFF
(
(
SELECT distinct ',' + QUOTENAME([MONTH])
FROM [HEADCOUNT]
WHERE [MONTH] BETWEEN '01-012018' AND '12-01-2018'
ORDER BY ',' + QUOTENAME([MONTH])
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
SET @query = 'SELECT [FTE/RATE CARD],' + @cols + ' FROM
(
SELECT [MONTH],[FTE/RATE CARD],[HC]
FROM [HEADCOUNT]
WHERE [CC-LOC] IN ([CC-LOC])
) x pivot (Sum ([HC]) for [MONTH] in (' + @cols + '))p' execute(@query)
解决方案
您需要为 select 语句生成另一个列列表,并用 ISNULL 包装各个月份,如下所示:
DECLARE @cols AS NVARCHAR(MAX),
@selectCols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @selectCols = STUFF
(
(
SELECT distinct ', ISNULL(' + QUOTENAME([MONTH]) + ', 0) AS ' + QUOTENAME([MONTH])
FROM [HEADCOUNT]
WHERE [MONTH] BETWEEN '01-012018' AND '12-01-2018'
ORDER BY ',' + QUOTENAME([MONTH])
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
SELECT @cols = STUFF
(
(
SELECT distinct ',' + QUOTENAME([MONTH])
FROM [HEADCOUNT]
WHERE [MONTH] BETWEEN '01-012018' AND '12-01-2018'
ORDER BY ',' + QUOTENAME([MONTH])
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
SET @query = 'SELECT [FTE/RATE CARD],' + @selectCols + ' FROM
(
SELECT [MONTH],[FTE/RATE CARD],[HC]
FROM [HEADCOUNT]
WHERE [CC-LOC] IN ([CC-LOC])
) x pivot (Sum ([HC]) for [MONTH] in (' + @cols + '))p' execute(@query)
推荐阅读
- d3.js - 如何向 D3.data() 提供更新的数据?
- python - 评估多元均匀分布的 PDF
- android-studio - 膨胀类 com.iammert.library.readablebottombar.ReadableBottomBar 时出错
- swift - UIKit:如何创建一个基础的、重用的 XIB 文件并在其中添加不同的内容?
- python - 如何扩展 xaxis 以专注于散点图上的气泡?
- android - 如何使RelativeLayout中的两个元素底部对齐?
- google-apps-script - 使用 Google Appscript 发送的 Google Workspace 电子邮件出现错误。消息被阻止 您的消息已被阻止
- authentication - 无 SAML/OAuth/OIDC 的单点登录 (SSO)
- azure - 尝试添加角色时出现 Azure cli 命令错误
- scala - 删除与架构不对齐的记录:Parquet:Spark scala