SQL Server data column to Horizontal Column

-- Take AdventureWorks database as example
Declare @ fromDate datetime, @ dueDate datetime
Select @ fromDate = '2014-09-01 ', @ dueDate = '2014-09-16'

Select top 100 a. ProductId, convert (nvarchar (10), B. OrderDate, 20) as OrderDate, count (*) as ProductCount
Into #
From Sales. SalesOrderDetail
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 #
Select ProductId, 'Total', sum (ProductCount)
From #
Group by ProductId

Declare @ SQL varchar (8000)
Set @ SQL = 'select produd d'

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 #