DECLARE	@MOnthStart DATETIME,
	@Tomorrow DATETIME,
	@YearStart DATETIME

SELECT	@MonthStart = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'),
	@Tomorrow = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()), '18991231'),
	@YearStart = DATEADD(YEAR, DATEDIFF(YEAR, '19000101', GETDATE()), '19000101')

SELECT		Product,
		Oranges,
		Bananas,
		Pears,
		[Total fruit]
FROM		(
			SELECT		CONVERT(VARCHAR(8), dt, 112) AS Product,
					SUM(CASE WHEN Category = 'Oranges' THEN Items ELSE 0 END) AS Oranges,
					SUM(CASE WHEN Category = 'Bananas' THEN Items ELSE 0 END) AS Bananas,
					SUM(CASE WHEN Category = 'Pears' THEN Items ELSE 0 END) AS Pears,
					SUM(Items) AS [Total fruit],
					1 AS SortOrder
			FROM		Table1
			WHERE		dt >= @MonthStart
					AND dt < @Tomorrow
			GROUP BY	CONVERT(VARCHAR(8), dt, 112)

			UNION ALL

			SELECT		'mtd' AS Product,
					SUM(CASE WHEN Category = 'Oranges' THEN Items ELSE 0 END) AS Oranges,
					SUM(CASE WHEN Category = 'Bananas' THEN Items ELSE 0 END) AS Bananas,
					SUM(CASE WHEN Category = 'Pears' THEN Items ELSE 0 END) AS Pears,
					SUM(Items) AS [Total fruit],
					2 AS SortOrder
			FROM		Table1
			WHERE		dt >= @MonthStart
					AND dt < @Tomorrow

			UNION ALL

			SELECT		'ytd' AS Product,
					SUM(CASE WHEN Category = 'Oranges' THEN Items ELSE 0 END) AS Oranges,
					SUM(CASE WHEN Category = 'Bananas' THEN Items ELSE 0 END) AS Bananas,
					SUM(CASE WHEN Category = 'Pears' THEN Items ELSE 0 END) AS Pears,
					SUM(Items) AS [Total fruit],
					3 AS SortOrder
			FROM		Table1
			WHERE		dt >= @YearStart
					AND dt < @Tomorrow
		) AS d
ORDER BY	SortOrder,
		Product