Sql Server 資料縱列轉橫列

-- 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 #