-2

Possible Duplicate:
dynamic sql pivot in sql server

How to alter this stored procedure for PIVOT output

SELECT COUNT(Rly) AS TheCount, Rly FROM  SPAD 

WHERE  Rly IN ('CR', 'ER', 'ECR', 'ECoR', 'NR', 'NCR', 'SR', 'SCR', 'SER', 'SECR', 'WR', 'WCR', 'Kolkata') 


GROUP BY Rly
Community
  • 1
  • 1
Sunil
  • 81
  • 3
  • 14
  • Are you wanting to pivot for each value of rly and have thecount show as its value? will rly ever have more values than what you listed? – xQbert Dec 22 '11 at 11:54
  • Needs more details of desired results so we don't have to guess. Also have you tried anything yourself? There are plenty of `PIVOT` examples [in BOL](http://msdn.microsoft.com/en-us/library/ms177410.aspx) – Martin Smith Dec 22 '11 at 11:56

1 Answers1

1

This ASSUMES that you have a finite number of rlys values; The only other current way to achieve this is to use DYNAMIC SQL if there are more values. (Searching for link)

Select
sum(case when rly = 'CR' then 1 else 0 end) as "CR",
sum(case when rly = 'ER' then 1 else 0 end) as "ER",
sum(case when rly = 'ECR' then 1 else 0 end) as "ECR",
sum(case when rly = 'ECoR' then 1 else 0 end) as "ECoR",
sum(case when rly = 'NR' then 1 else 0 end) as "NR",
sum(case when rly = 'NCR' then 1 else 0 end) as "NCR",
sum(case when rly = 'SR' then 1 else 0 end) as "SR",
sum(case when rly = 'SCR' then 1 else 0 end) as "SCR",
sum(case when rly = 'SER' then 1 else 0 end) as "SER",
sum(case when rly = 'SECR' then 1 else 0 end) as "SECR",
sum(case when rly = 'WR' then 1 else 0 end) as "WR",
sum(case when rly = 'WCR' then 1 else 0 end) as "WCR",
sum(case when rly = 'Kolkata' then 1 else 0 end) as "Kolkata"
FROM SPAD

EDITED After acceptance to change from count to sum.

Community
  • 1
  • 1
xQbert
  • 34,733
  • 2
  • 41
  • 62