3 Replies Latest reply on Aug 1, 2016 9:50 AM by rene

    Transpose Function Not Enough

    incentrik

      I am creating attribute KPI's within PI_AF.  The KPI attributes (Max, Min, Weight) are sub-attributes to the parent attribute.  As you can see in the pic below the parent attributes are in there own columns.  I need to group the columns with the same parent.  See second pic. The first query is SQL commander and the second query is SSMS.  I cannot use the second query in SQL commander due to pivot and temp table creation.  How can I get the same results in SQL commander as I did in SSMS?  Thanks for any feedback.

        • Re: Transpose Function Not Enough
          incentrik

          Updated view of the second pic above.

           

          • Re: Transpose Function Not Enough
            rene

            Hi Bryan,

             

            First of all there are some missing information. How are built your template(s) and transpose functions? To be more precise I would need to know.

            Please have a look at the following scenario:

             

            Element Template

             

            Transpose Snapshot Function

             

            CREATE FUNCTION [Sujan].[DataT].[TransposeSnapshot_Blower]

            AS

            [Sujan]..[TransposeSnapshot]

            <

                 N'Blower' /*template name*/,

                 N'\' /*attribute template path*/,

                 True /*include attribute subtree*/,

                 False /*values as VARIANT*/

            >

             

             

            Query:

            SELECT Name, 'Current',  [\Current] as Current, [\Current\Minimum] as Minimum, [\Current\Maximum] as Maximum, [\Current\Weight] as Weight

            FROM

            (

            SELECT eh.Path + eh.Name Element, eh.Name, ts.*

            FROM [Sujan].[Asset].[ElementHierarchy] eh

            CROSS APPLY [Sujan].[DataT].[TransposeSnapshot_Blower](eh.ElementID) ts

            WHERE eh.Path = N'\South Shelby\Blowers\'

            ) t

            UNION ALL

            SELECT Name, 'Run Status',  [\Run Status] as [Run Status], [\Run Status\Minimum] as [Minimum], [\Run Status\Maximum] as Maximum, [\Run Status\Weight] as Weight

            FROM

            (

            SELECT eh.Path + eh.Name Element, eh.Name, ts.*

            FROM [Sujan].[Asset].[ElementHierarchy] eh

            CROSS APPLY [Sujan].[DataT].[TransposeSnapshot_Blower](eh.ElementID) ts

            WHERE eh.Path = N'\South Shelby\Blowers\'

            ) t

            UNION ALL

            SELECT Name, 'Speed',  [\Speed] as Speed, [\Speed\Minimum] as Minimum, [\Speed\Maximum] as Maximum, [\Speed\Weight] as Weight

            FROM

            (

            SELECT eh.Path + eh.Name Element, eh.Name, ts.*

            FROM [Sujan].[Asset].[ElementHierarchy] eh

            CROSS APPLY [Sujan].[DataT].[TransposeSnapshot_Blower](eh.ElementID) ts

            WHERE eh.Path = N'\South Shelby\Blowers\'

            ) t

            OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

             

            Result:

            Regards,

            Rene