Home » SQL & PL/SQL » SQL & PL/SQL » Column to rows (Oracle 9i Windows2000)
Column to rows [message #314782] Thu, 17 April 2008 14:37 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Hi,
I have records like given below.
Quote:

ID A1 A2 A3 B1 B2 B3
-----------------------
1000,11,12,13,21,22,23
2000,31,32,33,41,42,43


CREATE TABLE TEST_TAB
(ID NUMBER, A1 NUMBER, A2 NUMBER, A3 NUMBER, B1 NUMBER, B2 NUMBER, B3 NUMBER);
INSERT ALL
INTO TEST_TAB VALUES(1000,11,12,13,21,22,23)
INTO TEST_TAB VALUES(2000,31,32,33,41,42,43)
SELECT * FROM DUAL;

I want to display an output like given below
1000,11,12,13
1000,21,22,23
2000,31,32,33
2000,41,42,43

To achive this I have written the following query.
WITH SUMMARY AS
(SELECT * FROM TEST_TAB)
SELECT ID, A1, A2, A3 FROM SUMMARY
UNION ALL
SELECT ID, B1, B2, B3 FROM SUMMARY;

Any other best approch??
Re: Column to rows [message #314806 is a reply to message #314782] Thu, 17 April 2008 16:56 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and modify the query based on your requirement.

http://www.oracle-developer.net/display.php?id=412

Regards

Raj
Re: Column to rows [message #314812 is a reply to message #314782] Thu, 17 April 2008 17:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
There is no need for the outer query, just:

SELECT ID, A1, A2, A3 FROM test_tab
UNION ALL
SELECT ID, B1, B2, B3 FROM test_tab;
Re: Column to rows [message #315529 is a reply to message #314812] Mon, 21 April 2008 18:18 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Quote:

SELECT ID, A1, A2, A3 FROM test_tab
UNION ALL
SELECT ID, B1, B2, B3 FROM test_tab;



This is pretty simple approch. But it is inefficient when you use complex inline view instead of test_tab.
Re: Column to rows [message #315563 is a reply to message #314782] Mon, 21 April 2008 21:41 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> But it is inefficient when you use complex inline view instead of test_tab.

Could you describe it closely (eg. evaluating the view twice)?
You may try to re-write it as described in http://www.orafaq.com/forum/m/302474/96705/#msg_302474.
Previous Topic: Materialized View refresh
Next Topic: ORA-01722 INVALID NUMBER
Goto Forum:
  


Current Time: Sat Dec 03 00:52:39 CST 2016

Total time taken to generate the page: 0.08241 seconds