pivot

WITH 句でベースのSELECT文を書いて、PIVOT 句でPIVOT するのがよいと思う。

ベースのSELECT は、キー＋値な感じ。

-- service_name
set lin 200
col SRV_NAME for a20
with ses as (
    select inst_id, substr(service_name, 1, 16) as srv_name, count(*) as cnt
    from
        gv$session
    where
        type='USER'
    group by
        inst_id, substr(service_name, 1, 16)
)
select * from ses
 pivot ( sum(cnt) for inst_id in ( 1 as node1, 2 as node2, 3 as node3, 4 as node4, 5 as node5, 6 as node6) )
order by 1;

ユーザー名のバージョンも。

-- username
set lin 200 pages 100
col username for a20
with ses as (
    select inst_id, username, count(*) as cnt
    from
        gv$session
    group by
        inst_id, username
)
select * from ses
 pivot ( sum(cnt) for inst_id in ( 1 as node1, 2 as node2, 3 as node3, 4 as node4, 5 as node5, 6 as node6) )
order by 1;