公用表表达式上的 SQL 数据透视表

Question

我正在尝试使用公用表表达式创建数据透视表，但我的数据透视表在数据透视列中返回 NULL 值。当我从 cte 中选择 * 时，我的结果如我所料返回。我需要一些帮助来弄清楚为什么我的数据透视表无法正常工作。

我的公用表表达式连接两个表，如下所示:

生产线

ID | ProductionLine--------------------1  | Line A2  | Line B

生产数据

ID | ProductionLine_ID | UnitsProduced | ProductionDate-------------------------------------------------------1  | 1                 | 200           | 2014-09-182  | 2                 | 50            | 2014-09-183  | 1                 | 100           | 2014-09-19

所有 ID 都是 int 字段。 UnitsProduced 是一个整型字段。 ProductionDate 是一个日期时间字段

这是我的sql查询

;with cte as (select pl.ProductionLine as ProductionLine, pd.ProductionDate as ProductionDate, pd.UnitsProduced as UnitsProduced from ProductionLine pljoinProductionData pd on pl.ID = pd.ProductionLine_ID)select * from (    select ProductionLine,     ProductionDate,     UnitsProduced    from  cte) xpivot(    max(UnitsProduced)     for ProductionLine in ([Line A])) pvt

我应该得到如下所示的结果:

ProductionDate | Line A-----------------------2014-09-18     | 2002014-09-19     | 100

但是，我的结果是这样的:

ProductionDate | Line A-----------------------2014-09-18     | NULL2014-09-19     | NULL

任何帮助将不胜感激

谢谢

Answer 1

如果数据有 NULL，select 子句执行类似这样的操作是非常常见的:

SELECT ISNULL([FieldName],0) as [FieldName] FROM SOMTEABLE

使用 PIVOT 的查询没有什么不同，您仍然可以通过 SELECT 子句 解决 NULL 问题，尽管这里的一个问题是您使用了“select * "并且此快捷方式隐藏了您可以解决问题的位置。

使用 CTE 的语法。请注意现在详细说明了“select *”的内容，并使用了 ISNULL(.....,0)。

;WITH
      cte
      AS (
            SELECT
                  pl.ProductionLine AS ProductionLine
                , pd.ProductionDate AS ProductionDate
                , pd.UnitsProduced  AS UnitsProduced
            FROM ProductionLine pl
                  JOIN ProductionData pd
                              ON pl.ID = pd.ProductionLine_ID
            )

SELECT
      ISNULL([Line A], 0) [Line A]
    , ISNULL([Line B], 0) [Line B]
FROM (
      SELECT
            ProductionLine
          , ProductionDate
          , UnitsProduced
      FROM cte
      ) x
PIVOT
      (
      MAX(UnitsProduced)
      FOR ProductionLine IN ([Line A], [Line B])
      ) pvt
;

我个人不建议使用 CTE，除非有明确的需要，但这里并非如此，简单的子查询将以稍微简单的语法完成完全相同的工作。

SELECT
      ISNULL([Line A], 0) [Line A]
    , ISNULL([Line B], 0) [Line B]
FROM (
      SELECT
            pl.ProductionLine AS ProductionLine
          , pd.ProductionDate AS ProductionDate
          , pd.UnitsProduced  AS UnitsProduced
      FROM ProductionLine pl
            JOIN ProductionData pd
                        ON pl.ID = pd.ProductionLine_ID
      ) x
PIVOT
      (
      MAX(UnitsProduced)
      FOR ProductionLine IN ([Line A], [Line B])
      ) pvt
;

See this working at SQLFiddle

COALESCE() 或 ISNULL()

我在上面使用了 ISNULL()，它是特定于 TSQL 的，PIVOT 语法也是如此。 Stackoverflow 建议尽可能使用标准 SQL，因此请注意在上面的查询中可以使用 COALESCE() 而不是 ISNULL()。但也请注意，这些功能并不完全相同。