February 22, 2010 at 12:52 pm
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 :
monthcountcode
68425DELIVERY
620CANCELLED
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
February 22, 2010 at 1:06 pm
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
February 22, 2010 at 1:15 pm
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]
February 22, 2010 at 2:19 pm
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.
February 22, 2010 at 2:34 pm
Glad I could help 🙂
Come back to this site if you need further assistance.
But remember: one post is better than 6! 😉
February 22, 2010 at 4:37 pm
Sure.I understand. I am sorry abt that. Thanks!
March 14, 2013 at 1:56 pm
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.
--Quote me
January 8, 2014 at 2:45 pm
In my experience pivot codes work slower with big tables than case ones.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply