1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

I'm attempting to create a query to transpose rows into columns

Discussion in 'SQL PL/SQL' started by Anu.h, Sep 5, 2017.

Tags:
  1. Anu.h

    Anu.h Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    This is the table I want to transpose into rows:

    target_name tgl round(avg(round ((AVERAGE/10),3)),3)
    health 30-AUG-17 0.816
    health 31-AUG-17 0.814
    health 01-SEP-17 0.633
    health 02-SEP-17 0.662
    health 03-SEP-17 1.119
    health 05-SEP-17 13.939

    and select query is,

    select * from
    (
    select target_name, trunc(rollup_timestamp) tgl, round(avg(round ((AVERAGE/10),3)),3) from
    MGMT$METRIC_HOURLY
    where
    rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
    and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
    and rollup_timestamp between sysdate - 7 and sysdate
    and METRIC_COLUMN = 'cpuUtil' and target_type='host'
    and METRIC_NAME = 'Load'
    group by target_name,trunc(rollup_timestamp)
    union
    select target_name,trunc(collection_timestamp) tgl, (round(value,3))
    from MGMT$METRIC_current where
    METRIC_COLUMN = 'cpuUtil' and target_type='host' and METRIC_NAME = 'Load' ) where target_name = 'ehgexnp2db01.ehealth.gov.au'
    order by 1,2;

    Result needed as:

    target_name 30-AUG-17 31-AUG-17 01-SEP-17 02-SEP-17 03-SEP-17 05-SEP-17
    health 0.816 0.814 0.633 0.662 1.119 13.939
     
    Last edited: Sep 5, 2017
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,517
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This:

    Code (SQL):
    target_name tgl round(avg(round ((AVERAGE/10),3)),3)
    health 30-AUG-17 0.816
    health 31-AUG-17 0.814
    health 01-SEP-17 0.633
    health 02-SEP-17 0.662
    health 03-SEP-17 1.119
    health 05-SEP-17 13.939
     
    Is not a table, it's a QUERY of a table. Provide a proper create table script and sample data if you honestly want anyone here to be able to assist you.
     
  3. Anu.h

    Anu.h Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    India
    Hi David,

    Thanks for the reply,

    wrote this dynamic query and facing below error, any suggesions??

    declare
    sql_stmt clob;
    pivot_clause DATE;
    begin

    select listagg('''' || trunc(rollup_timestamp) || '''', ',') within group (order by trunc(rollup_timestamp)) into pivot_clause from
    MGMT$METRIC_HOURLY
    where
    rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
    and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
    and rollup_timestamp between sysdate - 7 and sysdate
    and METRIC_COLUMN = 'cpuUtil' and target_type='host'
    and METRIC_NAME = 'Load' group by trunc(rollup_timestamp);
    sql_stmt := 'select * from
    (
    select target_name, trunc(rollup_timestamp) tgl,round(avg(round ((AVERAGE/10),3)),3) average from
    MGMT$METRIC_HOURLY
    where
    rollup_timestamp >= (trunc(rollup_timestamp)+8/24)
    and rollup_timestamp < (trunc(rollup_timestamp)+18/24)
    and rollup_timestamp between sysdate - 7 and sysdate
    and METRIC_COLUMN = ''''cpuUtil'''' and target_type=''''host''''
    and METRIC_NAME = ''''Load''''
    group by target_name, trunc(rollup_timestamp), rollup_timestamp
    union
    select target_name,trunc(collection_timestamp) tgl, (round(value,3))
    from MGMT$METRIC_current where
    METRIC_COLUMN = ''''cpuUtil'''' and target_type=''''host'''' and METRIC_NAME = ''''Load'''' ) PIVOT (max(average) for (tgl) in (' || pivot_clause || ')) where target_name = ''''health''''
    order by 1,2';

    execute immediate sql_stmt;
    end;
    /


    ERROR at line 1:
    ORA-01423: error encountered while checking for extra rows in exact fetch
    ORA-01489: result of string concatenation is too long
    ORA-06512: at line 5
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,517
    Likes Received:
    360
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Still no 'create table' statements, no sample data, so HOW can we test what you wrote to see why it's failing? Your 'dynamic query' is based on one or more tables, and we have no way of creating those correctly (meaning as your tables are defined). You wouldn't have a mechanic try to fix your car over the phone, so why do you expect us to 'fix' your queries without the proper tools?
     
    raovk13 likes this.