SQL Server删除透视中的空值

Question

我有一张基本的桌子。我想把它变成线性形式，这就是为什么我用Base table。我的查询是这样的：

select
[report_date] AS 'Date'
,[30],[percent_30] AS '%30'
,[45],[percent_45] AS '%45'
,[60],[percent_60] AS '%60'
,[75],[percent_75] AS '%75'
,[90],[percent_90] AS '%90'
,[105],[percent_105] AS '%105'
,[120],[percent_120] AS '%120'
,[TOTAL] AS 'Total Sales'
--,[total_percentage] AS 'Total Percent'
 from database.dbo.delivery_report_logs  
PIVOT(SUM(Sale_Count) 
      FOR description IN ([30],[45],[60],[75],[90],[105],[120],[TOTAL])) AS pvz

但是结果看起来像这样。

我要删除所有空值。有什么帮助吗？谢谢。

下面是我的预期输出：Sample Output

Answer 1

select
[report_date] AS 'Date'
,[30],[percent_30] AS '%30'
,[45],[percent_45] AS '%45'
,[60],[percent_60] AS '%60'
,[75],[percent_75] AS '%75'
,[90],[percent_90] AS '%90'
,[105],[percent_105] AS '%105'
,[120],[percent_120] AS '%120'
,[TOTAL] AS 'Total Sales'
--,[total_percentage] AS 'Total Percent'
 from database.dbo.delivery_report_logs  
PIVOT ISNULL(SUM(Sale_Count),0) 
      FOR description IN ([30],[45],[60],[75],[90],[105],[120],[TOTAL])) AS pvz

Answer 2

这个问题已经解决了。我使用临时表来透视数据并将其插入到日志表中，它会给出我需要的结果。

Answer 3

您还可以使用这样的查询，该查询使用case表达式并与之聚合

SELECT  report_date,

        SUM(CASE WHEN Description = '30' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '30' THEN percent_30 END) AS [percent_30],

        SUM(CASE WHEN Description = '45' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '45' THEN percent_45 END) AS [percent_45],

        SUM(CASE WHEN Description = '60' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '60' THEN percent_60 END) AS [percent_60],

        SUM(CASE WHEN Description = '75' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '75' THEN percent_70 END) AS [percent_70],

        SUM(CASE WHEN Description = '90' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '90' THEN percent_90 END) AS [percent_90],

        SUM(CASE WHEN Description = '105' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '105' THEN percent_105 END) AS [percent_105],

        SUM(CASE WHEN Description = '120' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = '120' THEN percent_120 END) AS [percent_120],

        SUM(CASE WHEN Description = 'TOTAL' THEN sale_count END) AS [30],
        SUM(CASE WHEN Description = 'TOTAL' THEN total_percentage END) AS [total_percentage]

FROM    database.dbo.delivery_report_logs

GROUP BY report_date

如果每个report_date/percentate只有一个值，则还可以使用MAX或MIN代替SUM