March 9, 2009 at 10:06 am
Hi All.. I have the following query/view:
Use [Database1]
Go
DECLARE @SchemaName NVARCHAR(255)
DECLARE @TableName NVARCHAR(255)
SET @SchemaName = 'dbo'
SET @TableName = 'table1, table2'
SELECT
sys.schemas.NAME AS SchemaName,
sys.all_objects.NAME AS TableName,
sys.all_columns.NAME AS ColumnName,
sys.extended_properties.NAME AS PropertyName,
sys.extended_properties.VALUE AS PropertyValue
FROM
sys.all_columns INNER JOIN
sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id INNER JOIN
sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id INNER JOIN
sys.extended_properties ON sys.all_columns.object_id = sys.extended_properties.major_id AND sys.all_columns.column_id = sys.extended_properties.minor_id
WHERE
(sys.schemas.name = @SchemaName) AND (sys.all_objects.name = @TableName) AND (sys.all_objects.type='U')
This returns data in the following format:
SchemaNameTableName ColumnNamePropertyNamePropertyValue
---------- --------- ---------- ----------------------------
dbo table1column1uepBusinessNameBScolumn1
dbo table1column1uepDataClassificationDCcolum1
dbo table2column2uepBusinessNameBScolumn2
dbo table2column2uepDataClassificationDCcolum2
But what I want to get is:
SchemaNameTableNameColumnNameuepBusinessNameuepDataClassification
---------- ------------------- ------------------------------------
dbo table1 column1BScolumn1 DCcolumn1
dbo table2column2BScolumn2 DCcolumn2
I know i need to pivot, but looking at pivots online etc all pivots use some form of Aggregation...
Please can anyone help... Thank you in advance...
March 9, 2009 at 10:11 am
The only way you pivot things right now is to use some form of aggregation. Since the data is essentially just groups of one, using MIN or MAXC would give you the pivot operation you need.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 9, 2009 at 11:43 am
Matt's correct. You have to have a PIVOT to aggregate your rows onto the column. Otherwise, what's the use of the pivot?
Of course, you can put a dummy aggregation in there for pivoting, but that takes a little work and ingenuity.
March 9, 2009 at 11:48 am
Wait a minute. I just looked at your data again. You're not doing a pivot. Not even close. Pivot is where you change your columns into rows and rows into columns. It looks like you're just trying to change two column names and leave everything else the same.
The best way to do this is use CASE statements where you have the uepBusinessName and uepDataClassification.
Case PropertyName When uepBusinessName Then PropertyValue Else NULL End as uepBusinessName.
Something like the above. Of course, if you're trying to do something more dynamic, look at the information schema views and the COALESCE function.
March 9, 2009 at 9:13 pm
Look closer.... will someone actually have a table name of ['table1, table2']? As it currently stands, this code gives no returns... so what's the actual code and declarations?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 10, 2009 at 3:07 am
The full actual code is:
Use LIVE
Go
DECLARE @SchemaName NVARCHAR(255)
DECLARE @TableName NVARCHAR(255)
SET @TableName = 'tblmyTableExample'
SET @SchemaName = 'dbo'
SELECT
sys.schemas.NAME AS SchemaName,
sys.all_objects.NAME AS TableName,
sys.all_columns.NAME AS ColumnName,
sys.extended_properties.NAME AS PropertyName,
sys.extended_properties.VALUE AS PropertyValue
FROM
sys.all_columns INNER JOIN
sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id INNER JOIN
sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id INNER JOIN
sys.extended_properties ON sys.all_columns.object_id = sys.extended_properties.major_id AND sys.all_columns.column_id = sys.extended_properties.minor_id
WHERE
(sys.schemas.name = @SchemaName) AND (sys.all_objects.name = @TableName) AND (sys.all_objects.type='U')
I will be getting parameters set using SSRS parameters... I think it would be a pivot what i need, as I am grouping the Schema, tables and column names and using the values returned in the property and value to become Row headers and content for the row...
March 10, 2009 at 3:42 am
Figured it, Brandie you were right!... code full code below;
Use [udbDATABASENAME]
Go
DECLARE @SchemaName NVARCHAR(255)
DECLARE @TableName NVARCHAR(255)
SET @TableName = 'tblmyTablename'
SET @SchemaName = 'dbo'
SELECT
myTableA.SchemaName,
myTableA.TableName,
myTableA.ColumnName,
MAX(CASE myTableA.PropertyName When 'uepBusinessName' Then [myTableA].[PropertyValue] Else NULL END)AS uepBusinessname,
MAX(CASE myTableA.PropertyName When 'uepDataClassification' Then [myTableA].[PropertyValue] Else NULL END)AS uepDataClassification
FROM
(
SELECT
sys.schemas.NAME AS SchemaName,
sys.all_objects.NAME AS TableName,
sys.extended_properties.minor_id AS ObjectID,
sys.all_columns.NAME AS ColumnName,
sys.extended_properties.NAME AS PropertyName,
sys.extended_properties.VALUE AS PropertyValue
FROM
sys.all_columns INNER JOIN
sys.all_objects ON sys.all_columns.object_id = sys.all_objects.object_id INNER JOIN
sys.schemas ON sys.all_objects.schema_id = sys.schemas.schema_id INNER JOIN
sys.extended_properties ON sys.all_columns.object_id = sys.extended_properties.major_id AND sys.all_columns.column_id = sys.extended_properties.minor_id
WHERE
(sys.schemas.name = @SchemaName) AND (sys.all_objects.name = @TableName) AND (sys.all_objects.type='U')
)myTableA
GROUP BY
SchemaName,
TableName,
ColumnName,
ObjectID
ORDER BY
myTableA.ObjectID
Thank you all for your help...
March 10, 2009 at 4:28 am
Glad we could help. @=)
And thanks for posting your solution.
March 10, 2009 at 8:22 am
Also glad we could help.
One parting thought - the code you're using is the "old-style" method for pivoting data (what was used before the PIVOT command in 2005 or when you want to pivot in ways that PIVOT doesn't allow for). So - you're still pivoting, you're just not using the word PIVOT in your query....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply