PIVOT function in SQL Server 2005

PIVOT:

This is one of the most important enhancements to TSQL in SQL 2005 and is extremely useful for generating reports based on cross tab values.The PIVOT operator rotates rows into columns, optionally performing aggregations or other mathematical calculations along the way.


The syntax for the PIVOT relational operator is:


FROMtable_source
PIVOT ( aggregate_function ( value_column )
FOR pivot_column
IN (<column_list>)
) table_alias


table_source : The table where the data will be pivoted.
aggregate_function : The aggregate function that will be used against the specified column. pivot_column : The column that will be used to create the column headers.
column_list : The values to pivot from the pivot column.


For Example:


create table parameter
(
pid int,
pname varchar(50)
)
GO


create table TransactionInfo
(
TransId int,
pid int,
pvalue int
)
GO


insert into parameter values(1,'Para1')
GO
insert into parameter values(2,'Para2')
GO
insert into parameter values(3,'Para3')
GO
insert into parameter values(4,'Para4')
GO
insert into parameter values(5,'Para5')
GO

insert into TransactionInfo values(1,1,30)
GO
insert into TransactionInfo values(1,2,40)
GO
insert into TransactionInfo values(1,3,50)
GO
insert into TransactionInfo values(1,4,35)
GO
insert into TransactionInfo values(1,5,45)
GO
insert into TransactionInfo values(2,1,50)
GO
insert into TransactionInfo values(2,2,60)
GO
insert into TransactionInfo values(2,3,70)
GO
insert into TransactionInfo values(2,4,85)
GO
insert into TransactionInfo values(2,5,45)
GO


In TransactionInfo table row wise records are stored. If you need columnwise report we go for PIVOT functions.

select TransID,para1,para2,para3,para4,para5 from
(select a.pname,b.pvalue,TransID from parameter a,TransactionInfo b
where a.pid = b.pid and TransID in (1,2) ) as aa
pivot
(
sum(pvalue)
for pname in([para1],[para2],[para3],[para4],[para5])
) as bb
GO


The Following result set is obtained after executing the above query, which PIVOTS data based on the parameters thats been selected.


Output of above query is as follows