CODEWITHSUNDEEP
sqloracle-database
gowtham ragunath
gowtham ragunath

Reputation: 13

Oracle Query with null values

Below is the requirement, can you help me to get desired query..

create table sales(Order_ID number, item varchar2(20));

insert into sales values (10, 'RICE');
insert into sales values (10, 'WATER');
insert into sales values (10, 'SALT');
insert into sales values (20, 'TOMATO');
insert into sales values (20, 'ONION');
insert into sales values (30, 'OIL');
insert into sales values (30, 'EGG');
insert into sales values (40, 'CHICKEN');
insert into sales values (50, 'FISH');

I need output as below format.

Order_ID    ITEM
10          RICE
            WATER
            SALT
20          TOMATO
            ONION
30          OIL
            EGG
40          CHICKEN
50          FISH

Upvotes: 0

Views: 123

Answers (4)

Yasuyuki Uno
Yasuyuki Uno

Reputation: 2547

Use LAG(COLUMN_NAME) OVER (ORDER BY)

LAG(COLUMN_NAME) gets previous record. 

SELECT DECODE(ORDER_ID, PRE_ORDER_ID, null, ORDER_ID) AS ORDER_ID,
       ITEM
  FROM
    (SELECT ORDER_ID,
            LAG(ORDER_ID) over 
                (order by ORDER_ID) AS PRE_ORDER_ID,
            ITEM
       FROM SALES);

Upvotes: 1

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

I usually do it in SQL*Plus and it is all about formatting your output.

You could use BREAK ON column_name.

For example,

SQL> break on deptno
SQL> SELECT deptno, ename FROM emp order by deptno;

    DEPTNO ENAME
---------- ----------
        10 CLARK
           KING
           MILLER
        20 JONES
           FORD
           ADAMS
           SMITH
           SCOTT
        30 WARD
           TURNER
           ALLEN
           JAMES
           BLAKE
           MARTIN

14 rows selected.

Most of the GUI based client tools now support most of the SQL*Plus commands. For example, in SQL Developer, you could run as script i.e. F5. In PL/SQL Developer, there is a different window for SQL*Plus like environment. However, if you want pure SQL approach, I suggest @Boneist's solution.

Upvotes: 2

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

use below query for u r required o/p 

select case when rno=1
    then 
    order_id
    else
    null
    end
    as order_id,item
    from
    (
    select order_id,item, row_number() over (partition by order_id order by order_id)  rno from sales
    )

Upvotes: 1

Boneist
Boneist

Reputation: 23588

Use the row_number() analytic function plus a case statement in order to only populate the order_id for the first row:

select case when rn = 1 then order_id end order_id,
       item
from   (select order_id,
               item,
               row_number() over (partition by order_id order by item) rn
        from   sales);

  ORDER_ID ITEM                
---------- --------------------
        10 RICE                
           SALT                
           WATER               
        20 ONION               
           TOMATO              
        30 EGG                 
           OIL                 
        40 CHICKEN             
        50 FISH

Upvotes: 2

Related Questions