none
Using Pivot Table in SQL Query RRS feed

  • Question

  • Hi, 

    I have a query where I am using a Pivot Table so I can convert some of the row data to column.

    WITH agg AS 
       (
        select NodeID, 
               count(distinct cpuindex) as number_of_cpu, 
               case 
                   when count(distinct cpuindex) < 8 THEN 1
                   else count(distinct cpuindex) / 8 
               end AS number_of_cores
        from CPUMultiLoad_Detail 
        where nodeid in (select nodeid from nodesdata)
        group by NodeID
       )
    SELECT * FROM (
    SELECT cp.Environment, n.Caption,
           cs.ComponentName,cs.ComponentStatisticData, cs.ErrorMessage,
           agg.NodeID, agg.number_of_cpu, agg.number_of_cores, n.description
    FROM APM_CurrentStatistics cs 
    INNER JOIN APM_Application app 
      ON cs.ApplicationID = app.ID
      AND app.Name IN ('Oracle Database Licensing') 
    INNER JOIN NodesData n
      ON cs.NodeID = n.NodeID
      AND n.description NOT LIKE '%Windows%'
    INNER JOIN NodesCustomProperties cp 
      ON cp.NodeID = n.NodeID
    INNER JOIN agg
      ON cs.NodeID = agg.NodeID
    ) t
    PIVOT(
        FOR cs.ComponentName IN (
            [Oracle Version], 
            [Oracle Tuning Pack], 
            [Oracle Version], 
            [Real Application Clusters (RAC)]) 
    ) AS pivot_table;

    I have a query where I am using a Pivot Table so I can convert some of the row data to column.

    I am expecting [Oracle Version], [Oracle Tuning Pack], [Oracle Version], [Real Application Clusters (RAC)] to show as Columns and the Error Message as the corresponding Values. I am new to using Pivot Tables in SQL and the above query is not working. Any help will be greatly appreciated.

    Thanks



    Tuesday, June 2, 2020 5:55 PM