Dynamic PIVOT CLR

Question

Dynamic PIVOT CLR

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

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

michael bourgon SSC Eights! Points: 823 More actions · Answer 1

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 SSC Eights! Points: 823 More actions · Answer 2

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 SSC Rookie Points: 40 More actions · Answer 3

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 More actions · Answer 4

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

https://github.com/ewahner/SQL

Umer Akram SSC-Insane Points: 22209 More actions · Answer 5

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 SSC Enthusiast Points: 194 More actions · Answer 6

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 More actions · Answer 7

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

Eric Wahner-345205 Mr or Mrs. 500 Points: 544 More actions · Answer 8

Eric Wahner-345205

Mr or Mrs. 500

Points: 544

November 28, 2014 at 8:22 am

#1761452

https://github.com/ewahner/SQL

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 10

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)