Problem with dynamic pivot table

Posted on 2011-05-11
Last Modified: 2012-08-14
I am having problems with the following dynamic pivot table generation.  It is not sorting the columns correctly in the output.  Can someone help with why my code is not working?

DECLARE @Columns varchar(8000)
select @columns = isnull(@columns+',','') + '['+[STZAE]+']' from DRAP_ECO_1 group by [STZAE]

exec ('SELECT * 


   (SELECT [DOKNR], [STZAE] as EventCode, [DATUM] as EventDate from DRAP_ECO_1
    SELECT [DOKNR], [STZAE] as EventCode, [DATUM] as EventDate from DRAP_ECO_1) as src


   (max(eventdate) FOR Eventcode IN ('+@columns+') )AS pvt

order by 1,2')

Open in new window

Question by:Fairfield
    LVL 40

    Expert Comment

    >> It is not sorting the columns correctly in the output.

    It sorted on first column as mentioned in the query. What sort order are you looking for?
    LVL 4

    Expert Comment

    Are you looking for Sorting on Date ???

    Then Use Order By 3

    Author Comment

    I am wanting to order by column name [STZAE].
    LVL 22

    Accepted Solution

    Try changing
    select @columns = isnull(@columns+',','') + '['+[STZAE]+']' from DRAP_ECO_1 group by [STZAE]
    select @columns = isnull(@columns+',','') + '['+[STZAE]+']' from DRAP_ECO_1 group by [STZAE] order by [STZAE]

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    This video discusses moving either the default database or any database to a new volume.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now