Pivot Query- need help

  • 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

  • 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

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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.

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Sure.I understand. I am sorry abt that. Thanks!

  • 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

  • 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