Monday, May 4, 2009

Convert column as row in sql server using PIVOT function.

when we developing resporting application with sql server sometime need to convert column as row in SQL server. we can use PIVOT function of sql server to do this.
I have sum of Sold items in table this.

i want it as this.

this transposition can be achived by below SQL Script.

select EC_Items.ItemName, sum(ItemCount) ItemCount from EC_OrderDetail inner join EC_Items on(EC_Items.ID=EC_OrderDetail.ItemID)
Group by ItemID,EC_Items.ItemName
order by ItemID
declare @Columns varchar(4000)
set @Columns = (Select distinct [ItemName] from EC_OrderDetail inner join EC_Items on(EC_Items.ID=EC_OrderDetail.ItemID)for XML path(''))
set @Columns=replace(@Columns,'','[')
set @Columns=replace(@Columns,'
set @Columns=substring(@Columns,0,len(@Columns))
print @Columns
declare @Query nvarchar(4000)
set @Query=
from ( select [ItemName],ItemCount from EC_OrderDetail inner join EC_Items on(EC_Items.ID=EC_OrderDetail.ItemID)) as SourceTable
pivot( sum(ItemCount) for [ItemName] in (
'+@Columns+')) as pivoteTable;'
declare @Parameters as nvarchar(4000)
set @Parameters='@Columns varchar(4000)'
exec sp_executesql @Query,@Parameters,@Columns=@Columns