How to write Pivot query in SQL Server 2005

Pivot is a new clause introduced in SQL Server 2005; it allows you to rotate row level data into tabular data without use of CASE statement, the sql server 2000 we used to write case statement to rotate row data into tabular data, but now in SQL 2005, with PIVOT you can do the same thing by writing less code.

So you want to see how PIVOT Query looks like, ok, I will provide you complete example of PIVOT query

Follow the steps below; at the end you will get your PIVOT query

1. Create a table (for example purpose)

create table tPivotTable

(

Column1 int,

Column2 int

)

2. Insert some data in the table

insert into tPivotTable values (1, 1)

insert into tPivotTable values (2, 12)

insert into tPivotTable values (3, 133)

Now if you say Select * from tPivotTable

Your result will be

Column1	Column2
1	1
2	12
3	123

Now we will rotate rows into tabular format, all rows of Column2 will come in single row, so the result will look like

FirstRowAsFirstCol	SecondRowAsSecondCol	ThirdRowAsThirdCol
1	12	13

Here is the PIVOT query for above result

select distinct [1]as FirstRowAsFirstCol, [2] as SecondRowAsSecondCol, [3] as ThirdRowAsThirdCol

from

(

select column1, column2 from tpivotTable tp

) as H

pivot

(

avg(column2)

For Column1 in ([1], [2], [3])

) as pvt

Every PIVOT query involves an aggregation of some type, so you can omit the GROUP BY statement, as in above query I have taken average of Column2

For Column1 in ([1], [2], [3]) here you have to mention your row data which you want to select as column, so [1], [2], [3] are the row data of column1

Thus we have learned PIVOT query in SQL Server 2005