-- take AdventureWorks database as example
declare @fromDate datetime, @dueDate datetime
select @fromDate = '2003-09-01', @dueDate='2003-09-16'
select top 100 a.ProductId, convert(nvarchar(10), b.OrderDate, 20) as OrderDate, count(*) as ProductCount
into #
from Sales.SalesOrderDetail a
inner join Sales.SalesOrderHeader b on a.SalesOrderId = b.SalesOrderId
where b.OrderDate >= @fromDate and b.OrderDate <= @dueDate
group by a.ProductId, b.OrderDate
order by a.ProductId, b.OrderDate
insert into #
select ProductId, 'TOTAL', sum(ProductCount)
from #
group by ProductId
declare @sql varchar(8000)
set @sql = 'select ProductId '
select @sql = @sql + ', sum(case OrderDate when ''' + OrderDate + ''' then ProductCount else 0 end) as [' + OrderDate + ']'
from #
group by OrderDate;
set @sql = @sql + ' from # group by ProductId '
-- source
select * from # order by ProductId, OrderDate
-- transposed
exec(@sql)
drop table #
Kyle
