The idea is to have a heading for each date returned by the query. This could be any number of dates. I dynamically altered the #table which is ugly but there is no other way.
Ultimately I would like to reduce the amount of cursors but I'm not sure if it is possible. This is using the Northwind database on SQL Server 2000 (I know, its lifecycle has ended).
USE Northwind
DECLARE @startdate DATETIME, @enddate DATETIME, @sql VARCHAR(1000)
SELECT @startdate = '1998-01-01', @enddate = '1998-06-01'
DROP TABLE #OUTPUT
DROP TABLE #TEMP
CREATE TABLE #OUTPUT
(
product VARCHAR(50)
)
SELECT o.orderdate, p.ProductName
INTO #TEMP
FROM [Northwind].[dbo].[Orders] o
join [order details] od ON od.orderid = o.orderid
join Products p on p.ProductID = od.ProductID
WHERE o.OrderDate >= @startdate AND o.OrderDate < @enddate
--Cursor storage variables
DECLARE @prod VARCHAR(30), @orderdate VARCHAR(20), @cnt AS INT
--
--Add date column headings
DECLARE CUR1 CURSOR LOCAL FOR
SELECT DISTINCT(CONVERT(VARCHAR, orderdate, 101)) FROM #TEMP
OPEN CUR1
FETCH NEXT FROM CUR1 INTO @orderdate
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE #OUTPUT ADD [' + @orderdate + '] VARCHAR(20) DEFAULT 0')
FETCH NEXT FROM CUR1 INTO @orderdate
END
--
--Add left column containing list of products
INSERT INTO #OUTPUT (product)
SELECT DISTINCT(productname) FROM #TEMP
--
--Update all rows setting the approriate date column to the count
DECLARE CUR2 CURSOR LOCAL FOR
SELECT CONVERT(VARCHAR, orderdate, 101), productname, COUNT(*) as cnt
FROM #TEMP
GROUP BY CONVERT(VARCHAR, orderdate, 101), productname
OPEN CUR2
FETCH NEXT FROM CUR2 INTO @orderdate, @prod, @cnt
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @sql = 'UPDATE #OUTPUT set [' + @orderDate + '] = [' + @orderdate + '] + ' + CAST(@cnt AS VARCHAR) + ' WHERE LTRIM(RTRIM(product)) = ''' + LTRIM(RTRIM(REPLACE(@prod, '''', ''))) + ''' '
EXEC(@sql)
PRINT @sql
FETCH NEXT FROM CUR2 INTO @orderdate, @prod, @cnt
END
SELECT * FROM #OUTPUT
The result should look like this:
product 01/01/1998 01/02/1998
Alice Mutton 0 2
Aniseed Syrup 3 1
Boston Crab Meat 1 2
PIVOT
which is not supported on your version. If you're stuck with 2000, this blog post can probably help you \$\endgroup\$