What an odd requirement!

Mark - That's a pretty slick approach.

Using Eugene's suggestion of a Crosstab query, this is the best (or at least the least messy approach) I could come up with:

WITH Data (ID, Item, ShipTo, FCST01, FCST02, FCST03) AS (

SELECT 1, NULL, NULL, 20130701, 20130801, 20130901 UNION ALL

SELECT 1, 'Item1', 'DC01', 10, 0, 0 UNION ALL

SELECT 1, 'Item2', 'DC01', 1499, 1461, 1142 UNION ALL

SELECT 1, 'Item3', 'DC01', 37, 35, 0)

,Tally(n) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3)

SELECT c.Item

,c.ShipTo

,d.[date]

,Qty=MAX(d.Qty)

FROM Data a

CROSS APPLY Tally b

CROSS APPLY (

SELECT ID, Item, ShipTo, FCST01, FCST02, FCST03

FROM Data c

WHERE c.Item IS NOT NULL AND a.ID = c.ID

) c

CROSS APPLY (

SELECT CASE n WHEN 1 THEN a.FCST01 WHEN 2 THEN a.FCST02 WHEN 3 THEN a.FCST03 END

,CASE n WHEN 1 THEN c.FCST01 WHEN 2 THEN c.FCST02 WHEN 3 THEN c.FCST03 END

) d ([date], Qty)

WHERE a.Item IS NULL

GROUP BY a.ID, c.ShipTo, c.Item, d.[date]

ORDER BY d.[date], c.Item