Home » SQL & PL/SQL » SQL & PL/SQL » SQL query for formatted data display (oracle, 11gR2, RHEL)
SQL query for formatted data display [message #653941] Fri, 22 July 2016 12:00 Go to next message
9390512774
Messages: 103
Registered: January 2011
Location: hyd
Senior Member
Hi Experts,

I want to display data in the provided format from the EMP table in scott schema provided in oracle sample schemas.

/forum/fa/13202/0/



Thanks in advance.
  • Attachment: OutPut.JPG
    (Size: 30.84KB, Downloaded 366 times)
Re: SQL query for formatted data display [message #653942 is a reply to message #653941] Fri, 22 July 2016 12:36 Go to previous messageGo to next message
John Watson
Messages: 8592
Registered: January 2010
Location: Global Village
Senior Member
I would begin by writing the SELECT statement, and worry about the display format later. One way to get individual rows with subtotals is to use GROUP BY with a ROLLUP clause, http://docs.oracle.com/database/121/SQLRF/statements_10002.htm#i2066419

--update
this is a better reference,
http://docs.oracle.com/database/121/DWHSG/aggreg.htm#DWHSG8609

[Updated on: Fri, 22 July 2016 12:50]

Report message to a moderator

Re: SQL query for formatted data display [message #653943 is a reply to message #653941] Fri, 22 July 2016 13:40 Go to previous message
Solomon Yakobson
Messages: 3079
Registered: January 2010
Location: Connecticut, USA
Senior Member
Well, in SQL*Plus you could do something like:

SQL> break on deptno skip 1
SQL> compute sum label "Total Salary" of sal on deptno
SQL> column sal format 99999.99
SQL> column deptno format 999999999999
SQL> select  deptno,
  2          ename,
  3          job,
  4          sal
  5    from  emp
  6    order by deptno
  7  /

       DEPTNO ENAME      JOB             SAL
------------- ---------- --------- ---------
           10 CLARK      MANAGER     2450.00
              KING       PRESIDENT   5000.00
              MILLER     CLERK       1300.00
*************                      ---------
Total Salary                         8750.00

           20 JONES      MANAGER     2975.00
              FORD       ANALYST     3000.00
              ADAMS      CLERK       1100.00
              SMITH      CLERK        800.00
              SCOTT      ANALYST     3000.00
*************                      ---------
Total Salary                        10875.00

           30 WARD       SALESMAN    1250.00
              TURNER     SALESMAN    1500.00
              ALLEN      SALESMAN    1600.00
              JAMES      CLERK        950.00
              BLAKE      MANAGER     2850.00
              MARTIN     SALESMAN    1250.00
*************                      ---------
Total Salary                         9400.00


14 rows selected.

SQL> 

Otherwise, use GROUP BY GROUPING SETS:

SELECT  CASE GROUP_ID() + GROUPING(ENAME)
          WHEN 1 THEN DEPTNO
        END DEPTNO,
        CASE
          WHEN GROUPING(ENAME) = 0 THEN ENAME
          WHEN GROUP_ID() = 1 THEN 'Total Salary'
        END ENAME,
        JOB,
        CASE GROUP_ID() + GROUPING(ENAME)
          WHEN 1 THEN NULL
          ELSE TO_CHAR(SUM(SAL),'9999990.00')
        END SAL
  FROM  EMP E
  GROUP BY GROUPING SETS((DEPTNO),(DEPTNO),(DEPTNO,ENAME,JOB))
  ORDER BY E.DEPTNO,
           GROUP_ID(),
           E.ENAME NULLS FIRST
/

    DEPTNO ENAME        JOB       SAL
---------- ------------ --------- -----------
        10
           CLARK        MANAGER       2450.00
           KING         PRESIDENT     5000.00
           MILLER       CLERK         1300.00
           Total Salary               8750.00
        20
           ADAMS        CLERK         1100.00
           FORD         ANALYST       3000.00
           JONES        MANAGER       2975.00
           SCOTT        ANALYST       3000.00
           SMITH        CLERK          800.00
           Total Salary              10875.00
        30
           ALLEN        SALESMAN      1600.00
           BLAKE        MANAGER       2850.00
           JAMES        CLERK          950.00
           MARTIN       SALESMAN      1250.00
           TURNER       SALESMAN      1500.00
           WARD         SALESMAN      1250.00
           Total Salary               9400.00

20 rows selected.

SQL> 

SY.

[Updated on: Fri, 22 July 2016 13:42]

Report message to a moderator

Previous Topic: GETTING NULLS WHEN USING PIVOT
Next Topic: Set a range into in time
Goto Forum:
  


Current Time: Sat Sep 18 04:34:38 CDT 2021