You could make a Stored Procedure that does this:
DECLARE @iColumns INT, @sql VARCHAR(2500),@cFruitName VARCHAR(10),@cFruitID varchar(10)
SET @sql = ''
DECLARE curFruits CURSOR FOR SELECT DISTINCT FruitID,FruitName FROM Fruits
OPEN curFruits
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ', SUM(CASE FruitID WHEN ' + @cFruitID + ' THEN Amount ELSE 0 END) as [' + @cFruitName + ']'
FETCH NEXT FROM curFruits INTO @cFruitID,@cFruitName
END
close curFruits
DEALLOCATE curFruits
SET @sql = 'SELECT Basket' + @sql + ' FROM Baskets GROUP BY Basket'
print @sql
exec(@sql)
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters