Transpose In sql server(Its uses for convert Column to Rows and Rows to column)

As known SQL Server 2000 need some dynamic sqls to convert rows to columns or vice versa. PIVOT and UNPIVOT relational operator did the job of converting rows to columns and columns to rows respectively.we can transpose values from rows to columns in sql server 2005 using PIVOT relational operator,

Ex Of Transpose:


create table #t (
id int identity(1,1),
AJAN int,
AFEB int,
AMAR int,
FJAN int,
FFEB int,
FMAR int
)

insert into #t (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR) values (1,2,3,4,5,6)
insert into #t (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR) values (11,12,13,14,15,16)

select * from #t

select
id,
'A'+c as amonth,
sum(case when c1='A' then val else 0 end) as aval,
'F'+c as fmonth,
sum(case when c1='F' then val else 0 end) as fval
from (
select id, val, substring(col,1,1) as c1, substring(col,2,3) as c
from #t
unpivot (val for col in (AJAN,AFEB,AMAR,FJAN,FFEB,FMAR)) up
) t
group by id,c
order by id,c