Re: Oracle Sqlplus Report

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_at_freelists.org> 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

--
http://www.freelists.org/webpage/oracle-l