SQL Pivot Table On A Common Table Expression

Question

I am trying to do a pivot table using a common table expression, but my pivot table is returning NULL values in the pivoted columns. When I select * from cte, my results return just as I would expect. I could use some help figuring out why my pivot table isn't working correctly.

My common table expression joins two tables that look like this:

ProductionLine

ID | ProductionLine
--------------------
1  | Line A
2  | Line B

ProductionData

ID | ProductionLine_ID | UnitsProduced | ProductionDate
-------------------------------------------------------
1  | 1                 | 200           | 2014-09-18
2  | 2                 | 50            | 2014-09-18
3  | 1                 | 100           | 2014-09-19

All IDs are int fields. UnitsProduced is an int field. ProductionDate is a datetime field

Here is my sql query

;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 * from 
(
    select ProductionLine, 
    ProductionDate, 
    UnitsProduced
    from  cte
) x
pivot
(
    max(UnitsProduced) 
    for ProductionLine in ([Line A])
) pvt

I should get a result that looks like this:

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

However, my result looks like this:

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

Any help would be appreciated

Thanks

Answer 1

It's extremely common to see select clause do something like this if data has NULLs:

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

Well it's no different for a query using PIVOT, you still solve the issue of NULLs through the SELECT clause although one problem here is that you have used "select *" and this shortcut hides the positions where you can solve the problem.

Syntax using a CTE. Note what was "select *" is now detailed, and ISNULL(.....,0) used.

;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
;

Personally I don't recommend using CTE's unless there is a demonstrated need for it, which isn't true here and a simple subquery will do exactly the same job with slightly simpler syntax.

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() or ISNULL()

I have used ISNULL() in the above which is TSQL specific, and so is the PIVOT syntax. Stackoverflow recommend using standard SQL if possible so please note it would be possible to use COALESCE() instead of ISNULL() in the queries above. But also note those functions are not exactly the same.