Re: Oracle Sqlplus Report

Karth
I tried to check doc already for Listagg and as per my understanding found that it will put the value as one column with some delimiter. May be I am not correct going more details and it might allow data to appear in multiple columns. Sorry I was away and check another update from Binh which provide the output I am looking. TxSanjay

    On Saturday, April 29, 2017 2:36 PM, Karthikeyan Panchanathan <keyantech_at_gmail.com> wrote:  

 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#

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    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 

TxSanjay    

--

http://www.freelists.org/webpage/oracle-l Received on Wed May 03 2017 - 16:16:06 CEST