Home Forums SQL Server 2008 T-SQL (SS2K8) Pivot Query with Count RE: Pivot Query with Count

  • March 17, 2013 at 8:04 pm

    #1597954

    raym85 (3/17/2013)

    It can get a little tedious typing out all the months...

    Pivot is pretty Straight forward.

    I agree. That's another reason why I don't use pivot. You have to type the months and then copy and paste. If you want a total, it gets even more complicated.

    I extended the pivot code to include all months and made the necessary change (Convert the date to 3 letter month) to make it work against the million row table I generated for this test in my previous post. Compare the two for performance. Preaggregation will make it faster but it still won't be faster than the preaggregated cross tab.

    SELECT City,Delivery_type,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]

    FROM( SELECT City,Delivery_type,Date_Del = CONVERT(CHAR(3),Date_Delivered,107)

    FROM #TestData)PVT

    PIVOT

    (

    COUNT(DATE_DEL)

    FOR DATE_DEL IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])

    )AS PRT

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)