Re: SQL to create one row from multiple records - Revisited

[CRPence <CRPbottle@xxxxxxxxx>]

On 3/31/11 2:32 PM, Jim Essinger wrote:
> <<SNIP; see original message>>
>
> Is there a somewhat easy way to do this with an SQL statement or
> UDF,  or am I going to have to write a program to do this?

  Should a non-existent week for a particular year generate the NULL 
value versus the zero value?  The difference may be moot for the given 
scenario.  Generating a table [optionally in a recursive CTE] before the 
pivot query might be useful for reference in a partial outer join, to 
generate both a zero value for the "shipped" for any missing week 
numbers in each year and the NULL value for any of the [two possible] 
non-existent week numbers of that same year.?

  Otherwise, the CASE given in another reply might best add an ELSE 0 
in order to avoid the NULL value for each week without any values; i.e. 
to achieve the effect of "If a week is missing a record, the amount 
should be zero for that week bucket".  While both COALESCE and IFNULL 
are alternatives, adding the ELSE to an existing CASE is probably 
clearer.  If the default precision for the SUM aggregate function might 
not be desirable, consider also changing to specify the desired 
precision [and scale]; e.g. for both ELSE and CAST of the SUM:

   , dec(sum(case when week=01 then shipped else 0 end), 7) as wk01

Regards, Chuck