Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic PIVOT CLR


Dynamic PIVOT CLR

Author
Message
Eric Wahner-345205
Eric Wahner-345205
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 66
Comments posted to this topic are about the item Dynamic PIVOT CLR
rmcomics
rmcomics
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 83
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.
rgp151
rgp151
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 87
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...
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51797 Visits: 40308
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.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric Wahner-345205
Eric Wahner-345205
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 66
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.
Eric Wahner-345205
Eric Wahner-345205
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 66
@rmcomics

I 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.
jcrawf02
jcrawf02
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1558 Visits: 19324
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.
Well, no, that's not true, you just have to use dynamic SQL.

Something like this:

/*
Title: ExtProps.spjc_viewExtendedProperties
Author: Jon Crawford
Description: pivots the extended properties that are available in the database
Business Need: to search the db for existing objects
What is the user going to do with this?:
Known flaws: n/a
Revision 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)
AS
BEGIN


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



**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)

http://sqlblog.com/blogs/michael_coles/archive/2010/01/12/t-sql-tuesday-easy-extended-properties.aspx

---------------------------------------------------------
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."
JustASQLGuy
JustASQLGuy
SSC-Enthusiastic
SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)SSC-Enthusiastic (111 reputation)

Group: General Forum Members
Points: 111 Visits: 168
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?
Eric Wahner-345205
Eric Wahner-345205
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 66
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
Eric Wahner-345205
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 66
@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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search