PIVOT with an extra 'else/default/fallback' column?

Question

Is it possible to define a "fallback" or an "else" case for a PIVOT operation? Let me play with the sample from MSDN:

-- Pivot table with one row and five columns
SELECT
    'AverageCost' AS Cost_Sorted_By_Production_Days, 
    [0], [1], [2], [3], [4],
    [??magicalELSEkeyword??] as 'too many days'

FROM
(
    SELECT DaysToManufacture, StandardCost 
    FROM Production.Product
) AS SourceTable

PIVOT
(
    AVG(StandardCost)
    FOR DaysToManufacture IN
    (
        [0], [1], [2], [3], [4],
        [??magicalELSEkeyword??]
    )
) AS PivotTable;

Pivot behavior is quite obvious - it selects/case/groups over the occurences of specified list of values. What I'd like to see is a "fallback" case. In the above query, I'd like to fetch:

• average cost for 'instantaneous' 0-day process
• average cost for 1-day process
• ...
• average cost for 4-day process
• average cost for all the others

Please note that the list of cases "wanted" is constant and known. I do not want to produce pivot of an unknown-number-of-columns. I just want to have all nonmatching values grouped as simple result named "others"

I've not seen any such possibility in the T-SQL syntax reference on MSDN, I just find it hard to believe this is not available.. It seems to be such useful feature and trivial to implement, that it just hurts to ommit it..

Yep, I know that it would probably introduce a discussion whether and how to handle NULL values that appear in the 'other', but that's just two special cases.. they could be ignored like in any aggregating function -- that would be trivial to implement, and 100% fine and consistent with rest of SQL -- or could be handled by two different fallback keywords like ELSE-NOT-NULL vs. ELSE-OR-NULL..

Is this feature really missing?

Answer 1

Unfortunately, it is not possible to place the other in an ELSE statement so I would put the CASE in your subquery. Then you will PIVOT on those new values:

select *
from
(
  select StandardCost,
    case DaysToManufacture
      when 0 then '0'
      when 1 then '1'
      when 2 then '2'
      when 3 then '3'
      when 4 then '4'
      else 'Others' end as DaysToManufacture
  from Product
) src
pivot
(
  avg(StandardCost)
  for DaysToManufacture in ([0], [1], [2], [3], [4], [Others])
) piv

See SQL Fiddle with Demo