http://www.simple-talk.com/community/blogs/andras/archive/2007/09/14/37265.aspx

--Creating Table

Create Table Ex
(Project varchar(30),
 pass int, 
 month varchar(30) )


--Inserting Sample Data

Insert Into Ex
Select 'WSBS-AR',	 85,	'MAR-12'
Union ALL
Select 'WSBS-Interface',	74,	'MAR-12'
Union ALL
Select 'WSBS-Payments',	88,	'MAR-12'
Union ALL
Select 'WSBS-AR',	 92,	'FEB-12'
Union ALL
Select 'WSBS-Interface',	85,	'FEB-12'
Union ALL
Select 'WSBS-Payments',	65,	'FEB-12'
Union ALL
Select 'WSBS-AR',	 45,	'DEC-11'
Union ALL
Select 'WSBS-Interface',	25,	'DEC-11'
Union ALL
Select 'WSBS-Payments',	65,	'DEC-11'


--Query for your requirement

;With CTE
As
(
Select Project,
(Case When month = 'MAR-12' Then SUM(pass) Else NULL End) As MAR_12,
(Case When month = 'FEB-12' Then SUM(pass) Else NULL End) As FEB_12,
(Case When month = 'DEC-11' Then SUM(pass) Else NULL End) As DEC_11
From Ex
Group By Project, month)
Select a.Project, a.MAR_12, b.FEB_12, c.DEC_11 From CTE As a
JOIN CTE as b ON a.Project = b.Project
JOIN CTE as c ON a.Project = c.Project
Where a.MAR_12 IS NOT NULL AND b.FEB_12 IS NOT NULL AND c.DEC_11 IS NOT NULL

Select Project,
SUM(Case When month = 'MAR-12' Then pass Else 0 End) As MAR_12,
SUM(Case When month = 'FEB-12' Then pass Else 0 End) As FEB_12,
SUM(Case When month = 'DEC-11' Then pass Else 0 End) As DEC_11
From Ex
Group By Project

quote:

---

Originally posted by visakh16

No need of CTE and all those joins. simply this would do

Select Project,
SUM(Case When month = 'MAR-12' Then pass Else 0 End) As MAR_12,
SUM(Case When month = 'FEB-12' Then pass Else 0 End) As FEB_12,
SUM(Case When month = 'DEC-11' Then pass Else 0 End) As DEC_11
From Ex
Group By Project

and if you want to make it dynamic based on month value you need to use like this

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by vinu.vijayan

quote:

---

Originally posted by visakh16

No need of CTE and all those joins. simply this would do

Select Project,
SUM(Case When month = 'MAR-12' Then pass Else 0 End) As MAR_12,
SUM(Case When month = 'FEB-12' Then pass Else 0 End) As FEB_12,
SUM(Case When month = 'DEC-11' Then pass Else 0 End) As DEC_11
From Ex
Group By Project

and if you want to make it dynamic based on month value you need to use like this

http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

OOPS!!....So yumbarrassing....
How did I miss that??...Good one Visakh.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

---