Oracle Select Query Result In Horizontal Format
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Oracle Select Query Result In Horizontal Format

  1. #1
    Join Date
    Jun 2007
    Posts
    14

    Oracle Select Query Result In Horizontal Format

    HI
    I am running SQL query in sql plus using script.
    THE query is get count for jan 1st,2,3,4,5 from table.
    I need output for each day for 5 days.
    my query
    is

    SPOOL /test/emprpt.csv

    but, i need the output horizantal way look to format like this in excel.
    that is why using csv file to automatically format in excel
    .how can i get this?
    thanks.N.
    I get the output like this in excel.

    select 'jan1st' from dual;
    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
    select 'jan2nd' from dual;
    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
    and so on... for all 5 queries.

    output will be like this in excel.
    JAN1ST: 800
    JAN 2ND: 588
    JAN 3RD: 478
    JAN 4TH:123
    JAN 5TH: 789.
    but, i want output like this way in excel in horizontal way.


    JAN 1ST JAN 2ND JAN 3RD JAN 4TH JAN 5TH
    800 588 478 123 789

    SPOOL /test/emprpt.csv
    select 'jJAN 1ST, JAN 2ND, JAN3RD, JAN 4TH, JAN 5TH' FROM DUAL;
    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');

    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');

    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');

    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');

    select count(empstatus)
    from emp,dept
    where emp_id = dept_id
    and emptype = 'finance'
    and emp_TimeStamp1 >= TO_DATE('&startday 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp2 <= TO_DATE('&enddate 23:59:00','YYYY-MM-DD HH24:MI:SS');
    SPOOL OFF
    EndOfCat
    But how can i get sql query result in horizontal way.

    i run above query with change the variables for start date and end date in the script for jan1st,2,3,4,5.

    i looked pivot. i did not understand. how to write this to get the result
    in horizontal way.

    pl help.
    thx,N.

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    Code:
    SQL> select * from test_date;
    
         EMPID EMP_TIME   EMPSTATUS
    ---------- --------- ----------
             1 01-JAN-08        800
             1 02-JAN-08        588
             1 03-JAN-08        478
             1 04-JAN-08        123
             1 05-JAN-08        789
             2 01-JAN-08        111
             2 02-JAN-08        222
             2 03-JAN-08        555
             2 04-JAN-08        333
             2 05-JAN-08        444
    
    10 rows selected.
    
    SQL> select empid
      2     , max(decode(emp_time, '01-JAN-08', empstatus)) "JAN 1ST"
      3     , max(decode(emp_time, '02-JAN-08', empstatus)) "JAN 2ND"
      4     , max(decode(emp_time, '03-JAN-08', empstatus)) "JAN 3RD"
      5     , max(decode(emp_time, '04-JAN-08', empstatus)) "JAN 4TH"
      6     , max(decode(emp_time, '05-JAN-08', empstatus)) "JAN 5TH"
      7  from (select * from test_date)
      8  group by empid;
    
         EMPID    JAN 1ST    JAN 2ND    JAN 3RD    JAN 4TH    JAN 5TH
    ---------- ---------- ---------- ---------- ---------- ----------
             1        800        588        478        123        789
             2        111        222        555        333        444

  3. #3
    Join Date
    Jun 2007
    Posts
    14
    ebrian,
    Thanks for your reply.I did n't get it.
    I am creating csv file to automatically format into excel.pl see bellow the sql query. the query wroks fine.
    i got the result. But in excel it shows each select count value
    775 888 999 234 546 in one excel cell.
    In order to separate each count result in each separate cell in excel.
    i need extra comma(,) after each count to automatically format in excel.

    How do add extra comma in this sql query after each select statement.
    select
    (select count(name)
    from emp,dept
    where emp_id = dept_id
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate1# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate1# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as a,
    (select count(name)
    from ah,oh
    where ah_oh_idnr = oh_idnr
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate2# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate2# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as b,
    (select count(name)
    from ah,oh
    where ah_oh_idnr = oh_idnr
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate3# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate3# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as c,
    (select count(name)
    from ah,oh
    where ah_oh_idnr = oh_idnr
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate4# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate4# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as d,
    (select count(name)
    from ah,oh
    where ah_oh_idnr = oh_idnr
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate5# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate5# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as e,
    (select count(name)
    from ah,oh
    where ah_oh_idnr = oh_idnr
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate6# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate6# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as f,
    (select count(name)
    from ah,oh
    where ah_oh_idnr = oh_idnr
    and emp_type = 'FINS'
    and emp_TimeStamp2 >= TO_DATE('&startdate7# 00:00:01','YYYY-MM-DD HH24:MI:SS')
    and emp_TimeStamp3 <= TO_DATE('&enddate7# 23:59:59','YYYY-MM-DD HH24:MI:SS')) as g,
    from dual;
    SPOOL OFF
    EndOfCat

  4. #4
    Join Date
    Jun 2007
    Posts
    14
    Thanks all.
    I got it.
    thx,n.

  5. #5
    Join Date
    Dec 2012
    Location
    izmir, Turkey
    Posts
    1

    help please

    hi, this instance multiple tables (for example, 3 tables) how-to.


    SELECT C_TABLE.ID, C_TABLE.NAME, A_TABLE.ID, B_TABLE.SESSION , SUM(B_TABLE.DEPT_TOTAL), SUM(B_TABLE.ARREARS_TOTAL)
    FROM A_TABLE , B_TABLE , C_TABLE
    WHERE B_TABLE.A_TABLE_ID = A_TABLE.ID
    AND B_TABLE.C_TABLE_ID = C_TABLE.ID
    AND B_TABLE.C_TABLE_ID = A_TABLE.C_TABLE_ID
    AND A_TABLE.CLOSED = O
    AND B_TABLE.YEAR = 2012
    AND C.TABLE.ID = 2277

    GROUP BY C_TABLE.ID, C_TABLE.NAME, A_TABLE.ID, B_TABLE.SESSION
    ORDER BY B_TABLE.SESSION
    ;


    C_TABLE.ID C_TABLE.NAME A_TABLE.ID B_TABLE.SESSION B_TABLE.DEPT_TOTAL B_TABLE.ARREARS_TOTAL
    2277 MR.J**** 7222 1 45,24 0
    2277 MR.J**** 7222 2 45,24 0
    2277 MR.J**** 7222 3 45,24 0
    2277 MR.J**** 7222 4 45,24 45,24
    2277 MR.J**** 7222 5 45,24 45,24
    2277 MR.J**** 7222 6 59,54 59,54


    I want to report


    B_TABLE.SESSION
    C_TABLE.ID C_TABLE.NAME A_TABLE.ID 1 2 3 4 5 6
    2277 MR.J**** 7222 45,24 0 45,24 0 45,24 0 45,24 45,24 45,24 45,24 59,54 59,54


    thanks....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width