Have you checked LISTAGG function.
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
Karth
Sent from my IPhone
On Apr 29, 2017, at 1:41 PM, Sanjay Mishra (Redacted sender "smishra_97" for
DMARC) <dmarc-noreply@xxxxxxxxxxxxx> wrote:
I am running query to get the data in rows to be converted to column as shown
below and so need help for SQL experts
SELECT ob.owner, ob.object_name, rowcnt, analyzetime
FROM sys.WRI$_OPTSTAT_TAB_HISTORY, dba_objects ob
WHERE owner='INFODBA'
and object_name in (select table_name from dba_tables where owner='DEMOUSER'
and num_rows > 100000)
and object_type in ('TABLE')
and object_id=obj#
order by savtime asc;
Original Format
================
OWNER OBJECT_NAME ROWCNT ANALYZETIME
---------- ------------------- ---------------
---------------------------
SAMPLE DEMO1 3233336 20170301
SAMPLE DEMO1 3233990 20170302
SAMPLE DEMO1 3234543 20170303
SAMPLE DEMO1 3235324 20170304
SAMPLE DEMO1 3235487 20170305
SAMPLE DEMO2 706595 20170301
SAMPLE DEMO2 706595 20170302
SAMPLE DEMO2 706595 20170303
SAMPLE DEMO2 706595 20170304
SAMPLE DEMO2 706595 20170305
SAMPLE DEMO3 9173498 20170301
SAMPLE DEMO3 9173498 20170302
SAMPLE DEMO3 9173498 20170303
SAMPLE DEMO3 9173498 20170304
SAMPLE DEMO3 9173498 20170305
Required Format
OBJECT_NAME 20170301 20170302 20170303 20170304 20170305
-------------- -------------- -------------- --------------
-------------- --------------
DEMO1 3233336 3233990 3234543 3235324
3235324
DEMO2 706595 706595 706595 706595 706595
DEMO3 9173498 9173498 9173498 9173498
9173498
Tx
Sanjay
