Home » SQL & PL/SQL » SQL & PL/SQL » sql query to display the repeated column values as null (Oracle 10g)
sql query to display the repeated column values as null [message #572533] Thu, 13 December 2012 00:29 Go to next message
queeny
Messages: 18
Registered: July 2011
Location: india
Junior Member
Hi,

I have table with the values as below.
C1	C2	C3	C4
NAME	JOHN	10	ABC
NAME	SMITH	30	DEF
NAME	ROBERT	60	XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1	C2	C3	C4
NAME	JOHN	10	ABC
	SMITH	30	DEF
	ROBERT	60	XYZ

I could do it using the below query using union with the help of rownum.
select * from (
    select rownum rn, c1,c2,c3,c4 from table_new 
) where rn =1
union
select * from (
    select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new 
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

Thanks
Queeny.

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 13 December 2012 01:24] by Moderator

Report message to a moderator

Re: sql query to display the repeated column values as null [message #572534 is a reply to message #572533] Thu, 13 December 2012 00:44 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Please post the output with in code tages.
if the name repeating you want space..
Re: sql query to display the repeated column values as null [message #572535 is a reply to message #572533] Thu, 13 December 2012 00:45 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql query to display the repeated column values as null [message #572536 is a reply to message #572534] Thu, 13 December 2012 00:58 Go to previous messageGo to next message
queeny
Messages: 18
Registered: July 2011
Location: india
Junior Member
Exactly. If the name is repeating, i want a space.
Re: sql query to display the repeated column values as null [message #572537 is a reply to message #572536] Thu, 13 December 2012 01:06 Go to previous messageGo to next message
BlackSwan
Messages: 23133
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: sql query to display the repeated column values as null [message #572541 is a reply to message #572537] Thu, 13 December 2012 01:23 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

I think you are excepting this one..
SELECT LAG(C1) over (order by C3) AS NAME1 ,C2,C3,C4     
  FROM test ORDER BY  NAME1
Re: sql query to display the repeated column values as null [message #572542 is a reply to message #572541] Thu, 13 December 2012 01:32 Go to previous messageGo to next message
Littlefoot
Messages: 19882
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
BREAK?
SQL> select job, ename, sal
  2  from emp
  3  order by job, ename;

JOB       ENAME             SAL
--------- ---------- ----------
ANALYST   FORD             3800
ANALYST   SCOTT            3800
CLERK     ADAMS            1900
CLERK     JAMES            1750
CLERK     MILLER           2100
CLERK     SMITH            1600
MANAGER   BLAKE            3650
MANAGER   CLARK            3250
MANAGER   JONES            3775
PRESIDENT KING             5800
SALESMAN  ALLEN            2400
SALESMAN  MARTIN           2050
SALESMAN  TURNER           2300
SALESMAN  WARD             2050

14 rows selected.

SQL> break on job
SQL> select job, ename, sal
  2  from emp
  3  order by job, ename;

JOB       ENAME             SAL
--------- ---------- ----------
ANALYST   FORD             3800
          SCOTT            3800
CLERK     ADAMS            1900
          JAMES            1750
          MILLER           2100
          SMITH            1600
MANAGER   BLAKE            3650
          CLARK            3250
          JONES            3775
PRESIDENT KING             5800
SALESMAN  ALLEN            2400
          MARTIN           2050
          TURNER           2300
          WARD             2050

14 rows selected.

SQL>
Re: sql query to display the repeated column values as null [message #572545 is a reply to message #572542] Thu, 13 December 2012 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or, if you don't use SQL*Plus:
SQL> select nullif(job, lag(job) over (order by job, ename)) job1, ename, sal
  2  from emp
  3  order by job, ename;
JOB1      ENAME             SAL
--------- ---------- ----------
ANALYST   FORD             3000
          SCOTT            3000
CLERK     ADAMS            1100
          JAMES             950
          MILLER           1300
          SMITH             800
MANAGER   BLAKE            2850
          CLARK            2450
          JONES            2975
PRESIDENT KING             5000
SALESMAN  ALLEN            1600
          MARTIN           1250
          TURNER           1500
          WARD             1250

Regards
Michel
Re: sql query to display the repeated column values as null [message #572547 is a reply to message #572545] Thu, 13 December 2012 01:46 Go to previous messageGo to next message
queeny
Messages: 18
Registered: July 2011
Location: india
Junior Member
Wow.. This is serving my purpose.
But what is this lag function and how it works?
Re: sql query to display the repeated column values as null [message #572549 is a reply to message #572547] Thu, 13 December 2012 01:55 Go to previous messageGo to next message
Littlefoot
Messages: 19882
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the documentation and what is it used for?
Re: sql query to display the repeated column values as null [message #572612 is a reply to message #572549] Fri, 14 December 2012 00:16 Go to previous messageGo to next message
sss111ind
Messages: 496
Registered: April 2012
Location: India
Senior Member

It's not using single sql.
SELECT   DECODE (rn, 1, job, NULL) job, ename, sal
    FROM (SELECT ROW_NUMBER () OVER (PARTITION BY job ORDER BY job, ename) rn,
                 job, ename, sal
            FROM scott.emp);


Regards,
Nathan

[Updated on: Fri, 14 December 2012 00:23]

Report message to a moderator

Re: sql query to display the repeated column values as null [message #572618 is a reply to message #572612] Fri, 14 December 2012 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PARTITION BY job ORDER BY job, ename

If you partition by JOB then it is useless to order by JOB as in each partition the job is the same one.

What does mean "It's not using single sql"?

Regards
Michel
Re: sql query to display the repeated column values as null [message #572628 is a reply to message #572618] Fri, 14 December 2012 02:05 Go to previous messageGo to next message
sss111ind
Messages: 496
Registered: April 2012
Location: India
Senior Member

Hi Micheal,

The query is using subquery so it's not single sql . And instead of order by job,ename we can use
order by ename

then it is probably ok.

Regards,
Nathan
Re: sql query to display the repeated column values as null [message #572633 is a reply to message #572628] Fri, 14 December 2012 04:19 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The query is using subquery so it's not single sql .


It is nevertheless one single sql statement.
Note that you can slighty change your statement to make it without any inline view:
SQL> SELECT DECODE (ROW_NUMBER () OVER (PARTITION BY job ORDER BY ename), 
  2                 1, job,
  3                 NULL) job1,
  4         ename, sal
  5  FROM emp
  6  order by job, ename
  7  /
JOB1      ENAME             SAL
--------- ---------- ----------
ANALYST   FORD             3000
          SCOTT            3000
CLERK     ADAMS            1100
          JAMES             950
          MILLER           1300
          SMITH             800
MANAGER   BLAKE            2850
          CLARK            2450
          JONES            2975
PRESIDENT KING             5000
SALESMAN  ALLEN            1600
          MARTIN           1250
          TURNER           1500
          WARD             1250

Which becomes closed to mine.
But I prefer to use LAG and NULLIF which semantics is a clearer implementation of the specification.

Regards
Michel

[Updated on: Fri, 14 December 2012 04:21]

Report message to a moderator

Re: sql query to display the repeated column values as null [message #572760 is a reply to message #572633] Mon, 17 December 2012 08:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2077
Registered: January 2010
Senior Member
Michel Cadot wrote on Fri, 14 December 2012 05:19
But I prefer to use LAG and NULLIF which semantics is a clearer implementation of the specification.


I prefer LAG without NULLIF (although some people may say it is not as clear):

select  lag(null,1,job) over(partition by job order by ename) job1,
        ename,
        sal
  from  emp
  order by job,
           ename
/

JOB1      ENAME             SAL
--------- ---------- ----------
ANALYST   FORD             3000
          SCOTT            3000
CLERK     ADAMS            1100
          JAMES             950
          MILLER           1300
          SMITH             800
MANAGER   BLAKE            2850
          CLARK            2450
          JONES            2975
PRESIDENT KING             5000
SALESMAN  ALLEN            1600

JOB1      ENAME             SAL
--------- ---------- ----------
          MARTIN           1250
          TURNER           1500
          WARD             1250

14 rows selected.

SQL> 


SY.
Re: sql query to display the repeated column values as null [message #572765 is a reply to message #572760] Mon, 17 December 2012 08:40 Go to previous messageGo to next message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is neat but not very clear. Wink

Regards
Michel
Re: sql query to display the repeated column values as null [message #572768 is a reply to message #572765] Mon, 17 December 2012 08:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2077
Registered: January 2010
Senior Member
Well, sometimes we are too focused on problem wording. Here, wording was to display repeated column values as null. If we re-word it as display column value as null except first time then LAG(null,1,job) becomes clear.

SY.
Re: sql query to display the repeated column values as null [message #572777 is a reply to message #572768] Mon, 17 December 2012 10:04 Go to previous message
Michel Cadot
Messages: 59968
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sure but not trivial!

Regards
Michel
Previous Topic: Rental Management ERD
Next Topic: Sql Query with Mutiple columns
Goto Forum:
  


Current Time: Wed Dec 17 17:22:02 CST 2014

Total time taken to generate the page: 0.05300 seconds