Dynamic PIVOT CLR

  • michael bourgon

    Right there with Babe

    Points: 751

    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

  • michael bourgon

    Right there with Babe

    Points: 751

    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"

  • jacksdan

    Grasshopper

    Points: 24

    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.

  • Eric Wahner-345205

    Mr or Mrs. 500

    Points: 544

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

    https://github.com/ewahner/SQL

  • twin.devil

    SSC-Insane

    Points: 22208

    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]

  • lacwill25

    Valued Member

    Points: 54

    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://www.sqlservercentral.com:80/Files/DynamicPivot.cs/16816.cs

    Is there another path to use?

  • Eric Wahner-345205

    Mr or Mrs. 500

    Points: 544

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

  • Eric Wahner-345205

    Mr or Mrs. 500

    Points: 544

    https://github.com/ewahner/SQL

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

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

  • Jeff Moden

    SSC Guru

    Points: 995118

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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