OraFAQ Forum: SQL & PL/SQL » Column to rows

Home » SQL & PL/SQL » SQL & PL/SQL » Column to rows (Oracle 9i Windows2000)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Column to rows [message #314782]

Thu, 17 April 2008 14:37

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??

Report message to a moderator

Re: Column to rows [message #314806 is a reply to message #314782]

Thu, 17 April 2008 16:56

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

Report message to a moderator

Re: Column to rows [message #314812 is a reply to message #314782]

Thu, 17 April 2008 17:58

Barbara Boehmer
Messages: 9106
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;

Report message to a moderator

Re: Column to rows [message #315529 is a reply to message #314812]

Mon, 21 April 2008 18:18

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.

Report message to a moderator

Re: Column to rows [message #315563 is a reply to message #314782]

Mon, 21 April 2008 21:41

flyboy
Messages: 1903
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.

Report message to a moderator

Previous Topic:	Materialized View refresh
Next Topic:	ORA-01722 INVALID NUMBER

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jul 17 22:52:38 CDT 2025