Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot Query Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 12:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:18 AM
Points: 17, Visits: 326
HI All,

I am trying to convert rows to columns using Pivot function. Here is my case

SELECT Month(a.createddate) as month,
count(statusid) as count,b.code
from ServiceRequest a,
StdActivity b
where a.createddate between '2009-06-01 00:00:00.000' and '2009-12-30 23:59:59.999'
--and statusid=10
and a.SRSetnumber like '%DELI%'
and a.statusid=b.StdActivityID
group by Month(a.createddate),a.statusid ,b.code
order by Month(a.createddate) ,a.statusid,b.code

Result Set :

month count code
6 8425 DELIVERY
6 20 CANCELLED


i want to use pivot function and convert the result set to


month delivery cancelled
6 8425
6 20



Any help would be greatly appreciated. Writing a reporting query and got stuck up with this .I havent used the pivot before and the help topics is little bit confusing to me.

Thanks
Post #870650
Posted Monday, February 22, 2010 1:07 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 40,210, Visits: 36,619
Please don't cross post. It just wastes people's time and fragments replies.
No replied to this thread please. Direct replies to http://www.sqlservercentral.com/Forums/FindPost870654.aspx



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #870670
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse