Oracle Sql col to row

Oracle sqlplus Change Row into Column?
sqlplus Change Row into Column?



CREATE or replace TYPE strings1 AS TABLE OF VARCHAR2(100);
/

CREATE or replace Function CONCAT_LIST1 ( lst IN strings1 )
RETURN VARCHAR2 AS
ret varchar2(1000);
BEGIN
FOR j IN 1..lst.LAST LOOP
ret := ret || lst(j)||',';
END LOOP;
RETURN ret;
END;
/



SELECT dbid,CONCAT_LIST1 (CAST( COLLECT(snap_id||'->'||to_char(begin_interval_time,'dd-mon-yy HH24')||'Hr') AS strings1 )) snap
FROM dba_hist_snapshot
where begin_interval_time between (sysdate -1) and sysdate
group by dbid
;


DBID
----------
SNAP
----------------------------------------------------------------------------------------------------
92516760
33324->08-oct-11 10Hr,33325->08-oct-11 11Hr,33326->08-oct-11 12Hr,33328->08-oct-11 14Hr,33330->08-oc
t-11 16Hr,33332->08-oct-11 18Hr,33334->08-oct-11 20Hr,33336->08-oct-11 22Hr,33338->09-oct-11 00Hr,33
323->08-oct-11 09Hr,33345->09-oct-11 07Hr,33344->09-oct-11 06Hr,33343->09-oct-11 05Hr,33342->09-oct-
11 04Hr,33341->09-oct-11 03Hr,33340->09-oct-11 02Hr,33339->09-oct-11 01Hr,33337->08-oct-11 23Hr,3333
5->08-oct-11 21Hr,33333->08-oct-11 19Hr,33331->08-oct-11 17Hr,33329->08-oct-11 15Hr,33327->08-oct-11
13Hr,





SELECT dbid, snap_id,to_char(begin_interval_time,'dd-mon-yy HH24')||'Hr' begin_time from dba_hist_snapshot
where begin_interval_time between (sysdate -1) and sysdate
order by 2
/

DBID SNAP_ID BEGIN_TIME
---------- ---------- -----------------------
92516760 33323 08-oct-11 09Hr
92516760 33324 08-oct-11 10Hr
92516760 33325 08-oct-11 11Hr
92516760 33326 08-oct-11 12Hr
92516760 33327 08-oct-11 13Hr
92516760 33328 08-oct-11 14Hr
92516760 33329 08-oct-11 15Hr
92516760 33330 08-oct-11 16Hr
92516760 33331 08-oct-11 17Hr
92516760 33332 08-oct-11 18Hr
92516760 33333 08-oct-11 19Hr
92516760 33334 08-oct-11 20Hr
92516760 33335 08-oct-11 21Hr
92516760 33336 08-oct-11 22Hr
92516760 33337 08-oct-11 23Hr
92516760 33338 09-oct-11 00Hr
92516760 33339 09-oct-11 01Hr
92516760 33340 09-oct-11 02Hr
92516760 33341 09-oct-11 03Hr
92516760 33342 09-oct-11 04Hr
92516760 33343 09-oct-11 05Hr
92516760 33344 09-oct-11 06Hr
92516760 33345 09-oct-11 07Hr

23 rows selected.


==


select t,max(sys_connect_by_path(object_name, ' ' )) obj_name
from (select OBJECT_TYPE t, object_name,
row_number() over(partition by OBJECT_TYPE order by object_name) rn
from user_objects
where object_name not like 'BIN%'
)
start with rn = 1
connect by prior rn = rn-1
and prior t = t
group by t;




T obj_name
------------------- ----------------------------------------------------------------------------------------------------
PROCEDURE SHOW_FKEYS
INDEX IDX_EMP_ANUJ NORMAL_T_BIT_BMX PARK_ACRES PARK_DESCR_PHONE PARK_NAME PK_DEPT PK_EMP
TABLE ADDRESSES BONUS DEPT EMP EMP_ANUJ EMP_DUP PARK SALGRADE T TEST T_BIT T_FETCH_FIRST_ROWS XXX


===========================

select OBJECT_TYPE t, object_name,
row_number() over(partition by OBJECT_TYPE order by object_name) rn
from user_objects
where object_name not like 'BIN%'
/