Home » SQL & PL/SQL » SQL & PL/SQL » Vertical display of unrealted columns
| Vertical display of unrealted columns [message #19831] |
Thu, 11 April 2002 12:03  |
Bluefish
Messages: 3
Registered: April 2002
|
Junior Member |
|
|
I would like to display two different set of columns , the two sets are not related to each other in the same resultset.
For example: Table1 has col1, col2....col10
I would like to display col1, col3, col4 based on condition CONDITION1 and col1, col3, col4 based on condition CONDITION2 all in the same select statement.i.e; the resultset should display as :
col1 col3 col4 col1 col3 col4. (The first three being from a different condition than the last three). And all these columns Must display one beside the other.
I hope I have explained this well enough.
Is there any way I can do this in oracle?
Thanks.
|
|
|
|
| Re: Vertical display of unrealted columns [message #19854 is a reply to message #19831] |
Sat, 13 April 2002 16:03  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
Since it is not clear how many rows are in the table and if there are different number of rows returned from two conditions, then how they would be aligned side by side in the same result set (if one returns 100 and latter returns 75, then the balance 25 of first result would appear with blanks in the columns of second condtion),
I could give you an example and you try it out.
And also your example shows the two sets of identical columns, I am not clear whether they are of same table and same as first set of 3 but with different values.
SELECT A.COL1, A.COL3, A.COL4, B.COL1, B.COL3, B.COL4 FROM (SELECT COL1,COL3,COL4, ROWNUM RNUM FROM MYTABLE WHERE first_condtion) A, (SELECT COL1,COL3,COL4,ROWNUM RNUM FROM MYTABLE WHERE second_condtion) B
WHERE A.RNUM = B.R.NUM(+);
I assume here that the rows returned for second set are lesser than the rows returned for first set. You can place that (+) for outer join at left hand side expression to add dummy lines for the rows returned extra fro second set.
OK, just try to look at the example below.
I just created a table contains the following data.
SQL> CREATE TABLE MY_TABLE (ENO NUMBER NOT NULL PRIMARY KEY,ENAM CHAR(10),EG CHAR(1),BSALARY NUMBER,
STATUS CHAR(3));
Table created.
SQL> INSERT INTO MY_TABLE SELECT * FROM MYP_TEST1;
5 rows created.
SQL> SELECT * FROM MY_TABLE;
ENO ENAM E BSALARY STA
---------- ---------- - ---------- ---
123 Steve A 2345 NEW
125 Chris A 3450 OLD
124 Bob B 3214 NEW
126 Michel A 3333 OLD
127 Anna B 1234 NEW
Now I just want to show the eno,enam,status with NEW onside and the OLD right side. Two different conditions, but come side by side from the same table.
Now I enter the sql query as following in the buffer (easy to modify if needed).
SQL> EDIT
Wrote file afiedt.buf
SELECT A.ENO,A.BSALARY,A.STATUS,B.ENO,B.BSALARY,B.STATUS FROM
(SELECT ENO,BSALARY,STATUS,ROWNUM RNUM FROM MY_TABLE WHERE STATUS = 'NEW') A,
(SELECT ENO,BSALARY,STATUS,ROWNUM RNUM FROM MY_TABLE WHERE STATUS = 'OLD') B
WHERE A.RNUM = B.RNUM(+)
/
Now run the same content.
SQL> R
1 SELECT A.ENO,A.BSALARY,A.STATUS,B.ENO,B.BSALARY,B.STATUS FROM
2 (SELECT ENO,BSALARY,STATUS,ROWNUM RNUM FROM MY_TABLE WHERE STATUS = 'NEW') A,
3 (SELECT ENO,BSALARY,STATUS,ROWNUM RNUM FROM MY_TABLE WHERE STATUS = 'OLD') B
4* WHERE A.RNUM = B.RNUM(+)
ENO BSALARY STA ENO BSALARY STA
---------- ---------- --- ---------- ---------- ---
123 2345 NEW 125 3450 OLD
124 3214 NEW 126 3333 OLD
127 1234 NEW
If you look at the result, the rows are returned by different conditions, one checks for 'NEW' and another for 'OLD', but both are returned side by side.
Hope this helps to resolve your query. Check it out.
Good luck :)
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 21:50:25 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
