SQL pivot table in general table expression
I'm trying to make a pivot table using a generic table expression, but my pivot table is returning null values in pivot columns. When I select * from cte, my results are returned as I expected. I could, with some help, figure out why my pivot table is not working correctly.
In my general table expression, two tables are concatenated that looks like this:
production line
ID | ProductionLine -------------------- 1 | Line A 2 | Line B
ProductionData h3>
ID | ProductionLine_ID | UnitsProduced | ProductionDate -------------------------------------------------- ----- 1 | 1 | 200 | 2014-09-18 2 | 2 | 50 | 2014-09-18 3 | 1 | 100 | 2014-09-19
All identifiers are int fields. UnitsProduced is an int int. 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 have to get a result that looks like this:
ProductionDate | Line A ----------------------- 2014-09-18 | 200 2014-09-19 | one hundred
However, my result looks like this:
ProductionDate | Line A ----------------------- 2014-09-18 | NULL 2014-09-19 | NULL
Any help would be appreciated
thank
source to share
It is extremely common to see a select clause do something like this if the data is NULL:
SELECT ISNULL ([FieldName], 0) as [FieldName] FROM SOMTEABLE
The good thing is that for a query using PIVOT
this is no different, you are still solving the NULL thru problem SELECT clause
, although one problem is that you used "select *" and that shortcut hides the positions where you can solve this problem.
Syntax using CTE. Notice that there is now "select *", and 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
;
Personally, I don't recommend using a CTE unless there is a proven need for it, which is wrong here, and a simple subquery will do the exact same job with a slightly simplified 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
;
COALESCE()
or ISNULL()
I have used ISNULL () in the above, which is TSQL specific, and also the PIVOT syntax. Stackoverflow recommends using standard SQL if possible, so note that in the queries above you can use COALESCE()
instead ISNULL()
. But also note that these functions are not exactly the same.
source to share