none
order by ..PIVOT SQL Server RRS feed

  • Question

  • Hi

    I have a pivot like this

    Select

    CalendarYear,CalendarMonth,


     


       

    count([ID])  as NumberOfOrders

      

         

     

    FROM[Database].[dbo].[DataTable]

    PIVOT


    (


      

    SUM(NumberOfOrders)FORCalendarMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])


    )ASpv1

    I wish to order by the CalendarYear, CalendarMonth, but cannot do this because I cannot place an order by before the pivot. How can I do this please

    please help


    Thursday, October 3, 2019 2:30 PM

All replies

  • Duplicate on SO
    Thursday, October 3, 2019 3:03 PM
  • Wrap it into a CTE (WITH clause) and then order however you wish.

    Arthur

    MyBlog


    Twitter

    Thursday, October 3, 2019 5:36 PM
    Moderator
  • Hi Billybobsonic,

    Here's the script you can refer to: 

    ;with cte as 
    (Select 
    CalendarYear,CalendarMonth,
    count([ID])as NumberOfOrders 
    FROM [Database].[dbo].[DataTable]
    group by CalendarYear,CalendarMonth)
    
    
    Select *
    FROM cte
    PIVOT
    (SUM(NumberOfOrders)FOR CalendarMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    )As Spv1
    order by calendaryear

    I'm not sure what is the meaning of 'order by calendarmonth' cuz when you pivot, the order of month is static---from 1-12. If you still have problems, feel free to let me know.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 4, 2019 9:01 AM
  • this did not work well sorry, as it gives me a row for 2018, and a row for 2019

    any more ideas, i will keep playing around with this code, but having no joy , please help

    Monday, October 7, 2019 1:57 PM
  • Hello,

    > gives me a row for 2018, and a row for 2019

    And, of course, you do not have any idea where to put WHERE-clause...  :)

    P.S. It's really rare when programmer can use an aggregation function and didn't understand filtration...


    Sincerely, Highly skilled coding monkey.

    Monday, October 7, 2019 2:41 PM
  • Hi Billy,

    Thank you for your reply.

    Could you  please share us your table structure and some sample data along with your expected result? So that we’ll get a right direction and make some tests.

    When you post your issue, please refer POSTING TIPS - Code, Images, Hyperlinks, Details.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 8, 2019 1:04 AM