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


No comments:

Post a Comment

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author