Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Dynamic PIVOT CLR Expand / Collapse
Author
Message
Posted Thursday, December 06, 2012 11:10 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Eric Wahner-345205 (12/6/2012)
vopipari (12/6/2012)
I've done this loads of time using TSQL sps and dynamic sql within, much in the same way as the CLR routine. Is there an advantage to using CLR over TSQL?


There are somethings that SQL does quite well and then there are somethings that .NET does well. What my approach attempts to do is simplify it so that you don't have to write a custom procedure for every PIVOT that you do. I too have used dynamic sql to solve this problem as well, and while I haven't done any performance testing to see which one performs better than the other, I can say that there should be fewer trips to the database with the CLR method.

This was more of an educational post to show how it could be done using a CLR procedure. I leave it up to the community to decide if this works for them or not.


Eric Wahner-345205 (12/6/2012)
I would agree when you are using known and predictable values like a set of dates or even dates that haven't occurred, creating a PIVOT in SQL is quite simple. This exercise was for those types of collections of data that are "dynamic" and ever changing. When you have an unpredictable set of data that you need to pivot, you really have no other choice.


Not quite true. It's "just" dynamic SQL and the same thing could be done there as what has been done in the managed code. Still, my hat's off to you for making life easier. I've always had a bad taste in my mouth about the current version of PIVOT especially when things like the ACCESS version of PIVOT works so very well.

Shifting gears a bit, have you done any testing performance wise?


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1393658
Posted Thursday, December 06, 2012 3:19 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:12 AM
Points: 2,627, Visits: 19,093
Eric Wahner-345205 (12/6/2012)
@jcrawf02

I think you confused my point with using a PIVOT vs dynamic PIVOT. When I said you have no choice, I was referring to doing a dynamic PIVOT. If you have unpredictable results you cannot write a PIVOT query without using dynamic sql to select the distinct rows that will ultimately be your columns.
I'm not sure how that's any different than what you did, you just passed the list of fields to the CLR where I didn't bother to pass them, just collected them from available fields in the system and stuffed them back into the SQL query I constructed. I could make mine do what yours does, without having to use a CLR.

Nice to know how to do it that way too, and well-written article, but it's not the *only* option.


---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
Post #1393769
Posted Thursday, December 06, 2012 5:32 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 5:57 PM
Points: 535, Visits: 729
Thank you for the great article! .NET world has always been a bit of a mystery to me (as I suspect for many other DB folks), I would only deal with it in SSIS scripts and similar circumstances. Your article is a great learning tool, not to mention the usefulness of being able to do dynamic PIVOTs.


Post #1393804
Posted Saturday, December 08, 2012 10:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
Sigh! SPAM reported.

--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1394378
Posted Monday, December 24, 2012 11:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55
Very nice article.
Post #1399945
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse