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

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