tags:

views:

105

answers:

1

Hi Stackoverflow, 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 2)

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.

A: 

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

Ralph