How to query multiple columns into more columns in oracle 10g

Question

This is possibly answered somewhere already but had trouble searching for it as I think I'm missing key terms.

What I want to do is convert multiple columns into more columns

so my query currently returns this:

col1   |   col2   | col3
__________________________
a       |  x       | 1
b       |  y       | 1
c       |  z       | 1
d       |  x       | 2
e       |  y       | 2
f       |  z       | 2
g       |  x       | 3
h       |  y       | 3
i       |  z       | 3

What I want to do is query it again to get:

col 1 | col 2 (shows column 1 and 2 where col3 is equal to 1)
col 3 | col 4 (shows column 1 and 2 where col3 is equal to 2)
col 5 | col 6 (shows column 1 and 2 where col3 is equal to 3)

Hope this makes sense. Haven't posted my query to get the first set of results as it's quite big (several union all's in it) and didn't think it was necessary. If it'll help I'll cut it down to something more manageable and post it.

Edit: Let me try explain better. On the new query I want to show 6 columns. The first two columns will be Select col1, col2 from 'original query' where col3 = 1

The second two columns (col3 and col4) will be
select col1, col2 from 'original query' where col3 = 2.

The final two columns will be (col5 and col6) will be
select col1, col2 from 'original query' where col3 = 3.

Your question is not clear enough as to what you want returned. I don't see any correlation between "col 3 | col 4" when column 1 and 2 are equal to 2? — Benny
You might also want add what column you want to JOIN by or line up by. from what i can guess from your question so far you could used a WITH and then join 3 subqueries (1 for each col3 value) — potatopeelings
It would help to have more details about the nature of the data itself that would require such a convoluted query request. The relational model does not place any importance on the order of columns. They are simply an unordered set of attributes for an entity. Thus, any request of "col1 = col2", "col3 = col4..." might be indicative of a faulty database schema. — Thomas
No tables in the database have a column named col1, i've just named them as that in the query i gave to stackoverflow for simplicity. — Ralph

Ralph Ralph · Accepted Answer · 2010-06-21T03:28:26

Ok I worked out a way (Not particularly elegant in my opinion but it works). Added the previous query in as a view Then did::

select a.col1, a.col2, b.col1, b.col2, c.col1, c.col2
 from view a, view b, view c
 where a.col3 = 1
  and  b.col3 = 2
  and  c.col3 = 3

also had to link up the primary key which I've excluded from here for simplicity. I think this is pretty much what potatopeelings was suggesting.

Thanks for your help guys, if anyone thinks of a better way of doing this please advise as I'm very much an amateur at the moment and well aware of that fact

How to query multiple columns into more columns in oracle 10g

1 Answers