CODEWITHSUNDEEP
sqlsql-serverpivot-table
macecase
macecase

Reputation: 147

Why an I getting Invalid Column Name in Pivot Table

I am making a Pivot Table in SQL. I am trying to get IsNull(ItemQty, 0) to work. The way I have the code below won't work as it throws the following error. If I change the top Select statement to just Select * from () it'll work. But, that doesn't allow me to do the IsNull then.

The ShipWeekMod and ItemQty are results of CASE statements in the vtc_ItemForecastSummary view. Any thoughts?

Msg 207, Level 16, State 1, Line 16 Invalid column name 'ShipWeekMod'. Msg 207, Level 16, State 1, Line 16 Invalid column name 'ItemQty'.

select
OfferTypeShipYear, EDPNO, OfferType, ShipYear, ShipWeekMod, IsNull(ItemQty, 0) as ItemQty
from
(
select OfferTypeShipYear, EDPNO, OfferType, ShipYear, ShipWeekMod, ItemQty as ItemQty from vtc_ItemForecastSummary
) DataTable
PIVOT
(
  SUM(ItemQty)
  FOR ShipWeekMod
  IN (
    [2],[4],[6],[8],[10],[12],[14],[16],[18],[20],[22],[24], [26],[28],[30],[32],[34],[36],[38],[40],[42],[44],[46],[48],[50],[52] 
  )
) PivotTable

Upvotes: 1

Views: 1954

Answers (1)

jpw
jpw

Reputation: 44891

You can't reuse the column that you pivot on in the select. Instead you have to apply the isnull function to each column returned by the pivot. As this can be tedious to do you might consider rewriting the query to use dynamic sql instead.

It should probably look like this:

select 
    OfferTypeShipYear, EDPNO, OfferType, ShipYear,
    isnull([2],0) as [2],
    isnull([4],0) as [4],
    isnull([6],0) as [6],
    isnull([8],0) as [8]
    ... etcetera

from (
    select 
       OfferTypeShipYear, EDPNO, OfferType, 
       ShipYear, ShipWeekMod, ItemQty 
    from vtc_ItemForecastSummary
) DataTable
PIVOT (
  SUM(ItemQty)
  FOR ShipWeekMod
  IN (
    [2],[4],[6],[8],[10],[12],[14],[16],[18],[20],[22],[24], 
    [26],[28],[30],[32],[34],[36],[38],[40],[42],[44],[46],[48],[50],[52] 
  )
) PivotTable

Upvotes: 2

Related Questions