| sql query to display the repeated column values as null [message #572533] |
Thu, 13 December 2012 00:29  |
 |
queeny
Messages: 11
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 #572612 is a reply to message #572549] |
Fri, 14 December 2012 00:16  |
 |
sss111ind
Messages: 268
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 #572633 is a reply to message #572628] |
Fri, 14 December 2012 04:19 |
 |
Michel Cadot
Messages: 54226
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 |
Solomon Yakobson
Messages: 1404
Registered: January 2010
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 14 December 2012 05:19But 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.
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
