0

So, let's consider my table as:

col1 data1 col2 data2
0     apple   1   frog
1     orange  0   dino
1     pine    0   dog
0     guava   0   cat

My result query should consist of data1 and data2 in a way that col1=1, then it shows that data1 value, and same with col2=1, then shows data2.

Ideal output:

data1 data2
orange frog
pine   NULL

Is that even possible?

Mihai Chelaru
  • 7,614
  • 14
  • 45
  • 51
Adshead
  • 107
  • 4
  • 10
  • Can you explain why would you get null as opposed to pine frog? As col1 for pine is 1 so that matches col2 of apple frog. Which is possible. – MikeT Nov 19 '18 at 03:05
  • Are the two pairs of columns independent of each other? Sounds like an undesirable database design. – wibeasley Nov 19 '18 at 05:17

1 Answers1

0

Anything is possible :) This sql will give you the result you want:

WITH t1 as 
(select rowid,col1,data1,
(select count(*) from thetable temp1 
  where temp1.col1 = tt.col1 and temp1.rowid < tt.rowid) seq1
from thetable tt
),
t2 as (select col2,data2,
(select count(*) from thetable temp2 
  where temp2.col2 = tt.col2 and temp2.rowid < tt.rowid) seq2
from thetable tt
)

select data1, data2
from t1
LEFT join t2 on col1 = col2 and seq1 = seq2
where col1 = 1
order by col1

The thought being:

  • Split the col1 data and the col2 data into separate tables (t1, t2)
  • Assign a sequence number (seq1, seq2) to each row. (With props to @meherzad for the subquery).
  • Then use the left join to assign col2's to col1's until you run out.
DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15