SQLServerCentral / Discuss content posted by Eric Wahner / Article Discussions / Article Discussions by Author / Dynamic PIVOT CLR / Latest Posts

RE: Dynamic PIVOT CLR

Neha05 — Mon, 24 Dec 2012 11:49:51 GMT

Very nice article.

RE: Dynamic PIVOT CLR

Jeff Moden — Sat, 08 Dec 2012 22:20:13 GMT

Sigh! SPAM reported.

RE: Dynamic PIVOT CLR

mishaluba — Thu, 06 Dec 2012 17:32:01 GMT

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.

RE: Dynamic PIVOT CLR

jcrawf02 — Thu, 06 Dec 2012 15:19:21 GMT

[quote][b]Eric Wahner-345205 (12/6/2012)[/b][hr]@jcrawf02I 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.[/quote]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.

RE: Dynamic PIVOT CLR

Jeff Moden — Thu, 06 Dec 2012 11:10:03 GMT

[quote][b]Eric Wahner-345205 (12/6/2012)[/b][hr][quote][b]vopipari (12/6/2012)[/b][hr]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?[/quote]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.[/quote][quote][b]Eric Wahner-345205 (12/6/2012)[/b][hr]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.[/quote]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?

RE: Dynamic PIVOT CLR

Eric Wahner-345205 — Thu, 06 Dec 2012 09:09:30 GMT

@jcrawf02I 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.

RE: Dynamic PIVOT CLR

Eric Wahner-345205 — Thu, 06 Dec 2012 09:05:28 GMT

[quote][b]vopipari (12/6/2012)[/b][hr]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?[/quote]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.

RE: Dynamic PIVOT CLR

vopipari — Thu, 06 Dec 2012 08:16:05 GMT

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?

RE: Dynamic PIVOT CLR

jcrawf02 — Thu, 06 Dec 2012 08:11:45 GMT

[quote][b]Eric Wahner-345205 (12/6/2012)[/b][hr]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, [b]you really have no other choice[/b].[/quote]Well, no, that's not true, you just have to use dynamic SQL. Something like this:[code="sql"]/*Title: ExtProps.spjc_viewExtendedPropertiesAuthor: Jon CrawfordDescription: pivots the extended properties that are available in the databaseBusiness Need: to search the db for existing objectsWhat is the user going to do with this?: Known flaws: n/aRevision History: Date Changes ------ ------- 9/13/2012 'initial implementation' 9/13/2012 modified to use dynamic SQL so that if new properties show up, they'll be included*/ALTER PROCEDURE [ExtProps].[spjc_viewExtendedProperties] (@searchTerm VARCHAR(255) = NULL)ASBEGIN DECLARE @sql VARCHAR(MAX), @properties VARCHAR(8000) --find all the user created extended properties, --but to force a reasonable order into the view we use a temp table to store distinct values -- before you use them in the dynamic SQL below DECLARE @table TABLE (name VARCHAR(255),orderValue int) INSERT INTO @table (name,orderValue) SELECT DISTINCT name , --here's where we pick the order of columns we want CASE name WHEN 'Title' THEN 1 WHEN 'Author' THEN 2 WHEN 'Description' THEN 3 WHEN 'Known Flaws' THEN 4 ELSE 99 --everything else will just show up after these ones, no particular order END AS orderValue FROM ExtProps.Properties --get rid of the Microsoft extended properties WHERE name NOT IN ('Caption','Long_Description', 'microsoft_database_tools_support', 'MS_Description', 'MS_DiagramPane1', 'MS_DiagramPaneCount') ORDER BY orderValue --============================== -- testing --SELECT * --FROM @table AS t --============================== --shove the names of all the distinct extended properties into a variable so we can use it in the dynamic SQL SELECT @properties= COALESCE(@properties ,'')+'['+CONVERT(VARCHAR(255),p.name)+'],' FROM @table AS p --get rid of the last comma that we added just above SET @properties = LEFT(@properties,LEN(@properties)-1) --============================== -- testing --SELECT @properties --============================== --force all of the unique property names into our PIVOT statement below, -- but hard-code the ltrim of the name and value (gets rid of leading blanks which cause ordering issues) -- and force the order by Title to make it look neat SET @sql = 'SELECT '+@properties+' FROM ( SELECT p.class , p.class_desc , p.object_schema , p.object_name , p.column_name , p.major_id , p.minor_id , LTRIM(CONVERT(varchar(255),p.name)) AS name , LTRIM(CONVERT(varchar(1000),p.value)) AS value FROM ExtProps.Properties AS p ) AS sourceTable PIVOT ( MIN(value) FOR [name] IN ( '+@properties+' ) ) AS PivotTable WHERE pivotTable.Title IS NOT NULL AND ( pivotTable.Title LIKE ''%'+COALESCE(@searchTerm,'')+'%'' OR pivotTable.Description LIKE ''%'+COALESCE(@searchTerm,'')+'%'' ) ORDER BY [Title]' --============================== -- testing --PRINT @sql --============================== --now execute the PIVOT statement we built to return data EXEC(@SQL)END[/code]**Edit - I should have had a link in there, in case anyone is wondering, I'm pivoting the extended properties that I populate using Michael Coles' sp's (very handy)[url=http://sqlblog.com/blogs/michael_coles/archive/2010/01/12/t-sql-tuesday-easy-extended-properties.aspx]http://sqlblog.com/blogs/michael_coles/archive/2010/01/12/t-sql-tuesday-easy-extended-properties.aspx[/url]

RE: Dynamic PIVOT CLR

Eric Wahner-345205 — Thu, 06 Dec 2012 07:41:55 GMT

@rmcomicsI don't see why not as long as your list is either based upon static information or populated from the same connection. Though I imagine if the data that you wanted to integrate into the list were on the same connection, that you would somehow integrate that into the select statement that is passed into the @query parameter.Perhaps you are just asking in general can you use LINQ within a CLR function? The answer would also be yes.

RE: Dynamic PIVOT CLR

Eric Wahner-345205 — Thu, 06 Dec 2012 07:38:00 GMT

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.

RE: Dynamic PIVOT CLR

Jeff Moden — Thu, 06 Dec 2012 07:21:03 GMT

Good article.I do want to point out that dynamic pivots, especially for things like "rolling date reporting" just aren't difficult to do in SQL Server.

RE: Dynamic PIVOT CLR

rgp151 — Thu, 06 Dec 2012 07:13:22 GMT

It's good, but it still doesn't resolve the biggest problem with SQL's PIVOT functionality (which is no fault of yours), which is that there is no way to create a dynamic pivot result-set that you can join to.For example, if you could create a dynamic pivot in a table valued function then you could join to the function, but there isn't a way to create a dynamic pivot UDF.PIVOT would be a heck of a lot more powerful if you could do that...

RE: Dynamic PIVOT CLR

rmcomics — Thu, 06 Dec 2012 05:07:41 GMT

Very nice article.A question:Is it possible for me to for example create a Class, generate a List of that class, populate it with the SQL Server query information, after that use LINQ to manipulate the information in this List and after return the content of this list on the procedure?Thanks a lot.

Dynamic PIVOT CLR

Eric Wahner-345205 — Wed, 05 Dec 2012 21:28:00 GMT

Comments posted to this topic are about the item [B]Dynamic PIVOT CLR[/B]