• Howdy. Interesting article, but I'm a .Net newbie. I'd been doing my own dynamic pivot, but Jeff's right - it's a pain to do.

    I had a dev compile the CS file I see the assembly in the database. However, I have no idea how to get a stored procedure to call it - he says he built it, but I don't see the SP. Is there something special that needs to be done, or an example you can post of the SP? Any help greatly appreciated.


  • Okay, speaking with Eric offline (much appreciated!), I got it compiled and working.

    1) there are a couple of typos in the script. Syx.Functions = Sys.Functions and UserDefindedFunctions = UserDefinedFunctions

    2) You need to add "References" in the Solution Explorer in Visual Studio/SSDT/BIDS. I added System, System.Data, and System.Xml (you check them on the list, then OK).

    Once I did that, I was able to Build/Compile/Publish the code to my database and call the procedure. Eric, this thing is awesome. Thanks!

    Three notes:

    1)@orderby needs to be called thusly:

    set @orderBy = 'order by PayMethod'

    2) Remember to insert into #temp!

    3) The pivot column can't ever be null. Eric suggested adding it to the WHERE clause, aka "where mypivotcolumn is not null"

  • The link to the DynamicPivot.cs file is broken, any chance you can update. Great idea by the way, how often do we need to do dynamic pivoting statements during the day to day, and it's a lot simpler than a SQL Statement to build it.

  • I have pushed my project to git. Please try the link below.


  • Nicely done. good article. Not really a fan of PIVOT thou.

    i would really interested to see the performance against to Cross tab queries as mentioned here Cross Tabs and Pivots, Part 1[/url]

  • Realize that this post may be somewhat dated...

    I am not able to access the content at the following link;


    Is there another path to use?

  • That is why I posted the project to github: https://github.com/ewahner/SQL

  • https://github.com/ewahner/SQL

  • I've yet to use PIVOT for anything. Maybe its my ignorance of its value.

  • Nothing against the CLR method but since it just builds dynamic SQL and you have to provide almost everything anyway, why not just build a function in T-SQL that builds the dynamic SQL?

    Shifting gears a bit, I wish MS would get off their duff's and make a truly useful and powerful PIVOT function in T-SQL like that found in MS Access.

    --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)

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply