主页 / dba / 问题 / 266747
Accepted
estonolose
Asked: 2020-05-08 23:34:46 +0800 CST

将值传递到列以在 Excel 中创建图形

  • 772

我正在尝试将句子的结果转换为列以在 excel 中创建图表。

我有这个 sql

select
hss.instance_number INST,
to_char(snap.begin_interval_time,'dd/mm/yyyy hh24:mi:ss') as Snap_Inicial,
hss.pool,hss.name,trunc(hss.bytes/1024/1024,2) SIZE_IN_MB
FROM DBA_HIST_SGASTAT hss,  dba_hist_snapshot snap
WHERE snap.instance_number = hss.instance_number
and hss.snap_id=snap.snap_id 
and hss.pool='shared pool'
and hss.instance_number=1
and hss.pool is not null
and BEGIN_INTERVAL_TIME BETWEEN sysdate-5 and sysdate
and hss.name in ('KGLHD','SQLA','KGLS')
ORDER BY INST, BEGIN_INTERVAL_TIME;

我得到这个输出

INST SNAP_INICIAL        POOL          NAME           SIZE_IN_MB
---- ------------------- ------------- -------------- ----------
   1 03/05/2020 09:00:30 shared pool   SQLA           150   
   1 03/05/2020 09:00:30 shared pool   KGLS           23    
   1 03/05/2020 09:00:30 shared pool   KGLHD          14    
   1 03/05/2020 09:30:32 shared pool   SQLA           160   
   1 03/05/2020 09:30:32 shared pool   KGLS           33    
   1 03/05/2020 09:30:32 shared pool   KGLHD          10

但我想要这样的输出

   INST SNAP_INICIAL        POOL          SQLA         KGLS    KGLHD 
---- ------------------- ------------- -----------  --------  ---------
   1 03/05/2020 09:00:30 shared pool      150        23        14
   1 03/05/2020 09:30:32 shared pool      160        33        10

这可能吗？

谢谢。

oracle oracle-11g-r2

1 个回答

  1. Best Answer

    你想要一个 Oracle PIVOT。

    SELECT * FROM (
  SELECT hss.instance_number AS inst,
         snap.begin_interval_time AS snap_inicial,
         hss.pool,
         hss.name,
         TRUNC(hss.bytes / 1024 / 1024, 2) AS size_in_mb         
    FROM dba_hist_sgastat hss
    JOIN dba_hist_snapshot snap
      ON snap.instance_number = hss.instance_number
     AND snap.snap_id = hss.snap_id
   WHERE hss.pool = 'shared pool'
     AND hss.instance_number = 1
     AND hss.pool IS NOT NULL
     AND snap.begin_interval_time BETWEEN SYSDATE - 5 AND SYSDATE
     AND hss.name IN ('KGLHD', 'SQLA', 'KGLS')
)
PIVOT (
SUM(size_in_mb) FOR name IN ('KGLHD', 'SQLA', 'KGLS')
)
ORDER BY inst ASC, snap_inicial ASC;
    • 1

相关问题