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- need help Expand / Collapse
Author
Message
Posted Monday, February 22, 2010 12:52 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 #870654
Posted Monday, February 22, 2010 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 13,872, Visits: 9,596
I'll say the same thing I always say: Do pivoting in the front end, not in the database. Easier, more efficient, gives the end user more options.

However, if you have to do it in the database for some reason, do you mean the results to be the way you presented them? It looks to me like the 20 should be in the last column and there should only be one row. Is that correct?


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #870668
Posted Monday, February 22, 2010 1:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 6,823, Visits: 13,270
Thanks to Gail for stopping me to post in the wrong thread just in time!!

And here's what I was about to post "over there":
(Note: I second Gus preferring to do it on the app side though...)
If it needs to be dynamic please have a look at the DynamicCrossTab article referenced in my signature.

DECLARE @tbl TABLE
([MONTH] INT, [COUNT] INT, code VARCHAR(30)
)
INSERT INTO @tbl
SELECT 6, 8425, 'DELIVERY' UNION ALL
SELECT 6, 20, 'CANCELLED'

-- option 1: PIVOT
SELECT [MONTH],[DELIVERY], [CANCELLED]
FROM
(
SELECT [MONTH] , [COUNT] , code
FROM @tbl
) p
PIVOT
(
SUM ([COUNT])
FOR code IN ( [DELIVERY], [CANCELLED])
) AS pvt

-- option 2: "classic" CASE statement
SELECT
[MONTH],
SUM(CASE WHEN code ='DELIVERY' THEN [COUNT] ELSE 0 END) AS [DELIVERY],
SUM(CASE WHEN code ='CANCELLED' THEN [COUNT] ELSE 0 END) AS [CANCELLED]
FROM @tbl
GROUP BY [MONTH]





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #870681
Posted Monday, February 22, 2010 2:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:18 AM
Points: 17, Visits: 326
Thank you very much for the script and your thoughts..The script works fine.However I will check if i can work on the front end rather than in back end.

Thanks Again.
Post #870736
Posted Monday, February 22, 2010 2:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 6,823, Visits: 13,270
Glad I could help
Come back to this site if you need further assistance.
But remember: one post is better than 6!




Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #870747
Posted Monday, February 22, 2010 4:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 2, 2013 10:18 AM
Points: 17, Visits: 326
Sure.I understand. I am sorry abt that. Thanks!
Post #870831
Posted Thursday, March 14, 2013 1:56 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 4:45 PM
Points: 308, Visits: 839
LutzM, thanks for the queries. Very nice learning experience.
Please elaborate, if you can, when to push back to front end. I have found it difficult to know when I should draw the line and say 'listen, I've sent you the data you need, now use the plethora of Excel data analysis nonmenclature, to pivot and to do your analysis on the report'. I found myself doing the trimming, the pivotting, the everything so that all the person on the excel end had to do is Insert Pivot Table and insert the fields into columns, rows, and filters.
General guidelines based on your experience, for pushing back on doing so much formatting on SQL end would be really appreciated.
Post #1431239
Posted Wednesday, January 8, 2014 2:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 12:06 PM
Points: 148, Visits: 293
In my experience pivot codes work slower with big tables than case ones.
Post #1529111
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse