This forum is currently locked. You can't register or post questions at this time. (read more)

SQL

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

chatwidravi
Member
Posts: 5
Joined: Thu Aug 08, 2013 8:28 am

SQL

Postby chatwidravi » Fri Aug 23, 2013 3:34 pm

I HAVE A TABLE WITH COLUMN X AND Y
X Y
A 1
A 2
B 3
B 4
AND I WANT THE OUTPUT LIKE

X Y
A 1
2
B 3
4
IN 10G THEN WHAT WOULD BE THE QUERY
Last edited by chatwidravi on Sun Aug 25, 2013 11:47 am, edited 1 time in total.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: SQL

Postby Tim... » Fri Aug 23, 2013 5:47 pm

Hi.

Here is the script to set up the test case.

Code: Select all

DROP TABLE t1 PURGE;
CREATE TABLE t1 (
  x  VARCHAR2(10),
  y  NUMBER
);

INSERT INTO t1 VALUES ('A', 1);
INSERT INTO t1 VALUES ('A', 2);
INSERT INTO t1 VALUES ('B', 3);
INSERT INTO t1 VALUES ('B', 4);
COMMIT;

SELECT * FROM t1;

X                   Y
---------- ----------
A                   1
A                   2
B                   3
B                   4

4 rows selected.

SQL>


When you ask questions you should send a script like this because it saves time for anyone trying to help you. You should make things as easy as possible for people to help you. :)

This query works:

Code: Select all

SELECT CASE
         WHEN x = LAG(x, 1, NULL) OVER (ORDER BY x, y) THEN
           TO_CHAR(y)
         ELSE
           x
       END AS x,
       CASE
         WHEN x = LAG(x, 1, NULL) OVER (ORDER BY x, y) THEN
           NULL
         ELSE
           y
       END AS y
FROM t1;

X                                                 Y
---------------------------------------- ----------
A                                                 1
2
B                                                 3
4

4 rows selected.

SQL>


The issues I see here are:

- Analytic functions rely on the order of the data in the analytic clause. I'm not sure your data will really be this simple, so I'm not sure this is actually a workable solution.
- Even though the data appears to be coming back in the correct order, Oracle do not guarantee the order of the data returned by a query without an explicit ORDER BY. What you have done with this requirement is made real ordering impossible. For example.

Code: Select all

SELECT CASE
         WHEN x = LAG(x, 1, NULL) OVER (ORDER BY x, y) THEN
           TO_CHAR(y)
         ELSE
           x
       END AS x,
       CASE
         WHEN x = LAG(x, 1, NULL) OVER (ORDER BY x, y) THEN
           NULL
         ELSE
           y
       END AS y
FROM t1
ORDER BY 1,2;

X                                                 Y
---------------------------------------- ----------
2
4
A                                                 1
B                                                 3

4 rows selected.

SQL>


Like I said, the first query seems to work, but there is no way to guarantee the data comes out in the correct order...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 7 guests