Post reply

Dynamic PIVOT CLR

  • May 13, 2014 at 2:45 pm

    #1713250

    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.

    Michael

  • May 16, 2014 at 8:49 am

    #1714218

    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"

  • November 27, 2014 at 10:39 pm

    #1761361

    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.

  • November 28, 2014 at 1:38 am

    #1761380

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

    https://github.com/ewahner/SQL

  • November 28, 2014 at 6:33 am

    #1761426

    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]

  • November 28, 2014 at 8:10 am

    #1761448

    Realize that this post may be somewhat dated...

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

    /SQLServerCentral2/Error404.aspx?404;http://qa.sqlservercentral.com:80/Files/DynamicPivot.cs/16816.cs

    Is there another path to use?

  • November 28, 2014 at 8:22 am

    #1761451

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

  • November 28, 2014 at 8:22 am

    #1761452

    https://github.com/ewahner/SQL

  • May 14, 2015 at 4:21 pm

    #1798032

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

  • May 14, 2015 at 7:45 pm

    #1798055

    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)
    Intro to Tally Tables and Functions

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

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