Pivoting Data Without Aggregation

  • 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...

  • 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?

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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...

  • 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...

  • Glad we could help. @=)

    And thanks for posting your solution.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • 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