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:
DateChanges
-------------
9/13/2012'initial implementation'
9/13/2012modified 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)
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."