v.montag v.montag - 3 months ago 12
SQL Question

create one list from to two colums

in need Help with oracle SQL.

i have a table with

from to
F B
B R
R D
E X
X Q


and i need the list

F
B
R
D
E
X
Q


so my problem is the jump from R-->D to E-->X

Edit: It's a big list with from and to, seperatet with a annother column as citerium. Normaly there is every from in the to column, so i used

SELECT from,snr as Nr FROM list where StrAbsNr = 1
union all
SELECT to,snr + 1 as Nr FROM list


to create a ordered list. But there are gaps in some parts, in the example there is D-->E missing
has anybody an idea ?

Answer

for your example this work:

WITH ft AS
  (SELECT 'f' vfrom, 'b' AS vto FROM dual  UNION ALL
  SELECT 'b' , 'r' FROM dual  UNION ALL
  SELECT 'r','d' FROM dual  UNION ALL
  SELECT 'e','x' FROM dual  UNION ALL
  SELECT 'x','q' FROM dual  )
SELECT a.a,  MAX(rn), MIN(ob)
FROM
  ( SELECT vfrom a , rownum rn, 1 ob FROM ft
  UNION ALL
  SELECT vto , rownum rn, 2 ob FROM ft
  ) a
GROUP BY a
ORDER BY MAX(rn), MIN(ob)

A    MAX(RN)    MIN(OB)
- ---------- ----------
f          1          1
b          2          1
r          3          1
d          3          2
e          4          1
x          5          1
q          5          2

 7 rows selected 

or analityc func row_number:

SELECT *
FROM
  (SELECT a.a,
    row_number() over (partition BY a order by rn, ob) rna,
    ob,
    rn
  FROM
    ( SELECT vfrom a, rownum rn, 1 ob FROM ft
    UNION ALL
    SELECT vto , rownum rn, 2 ob FROM ft
    ) a
  )
WHERE rna=1
ORDER BY rn,
  ob
A        RNA         OB         RN
- ---------- ---------- ----------
f          1          1          1
b          1          2          1
r          1          2          2
d          1          2          3
e          1          1          4
x          1          2          4
q          1          2          5

 7 rows selected