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
Change is inevitable... Change for the better is not.