As shown in the following form:
ID NAME
Zhang SAN 1

2, dick, and harry3 fifty

XUH SUBJECT
1, 2 Chinese
2, 3 mathematical
1, 2, 3 physical
1, 3 English
1 biological

Want to achieve as follows:
XUH NAME SUBJECT
1, 2, zhang SAN, li si Chinese
2, 3 li si, fifty and mathematical
1, 2, 3 zhang SAN, li si, fifty and physical
1, 3 threes, fifty and English
Zhang SAN biological
1
The trouble to give directions, thank you!

CodePudding user response:

A comma separated, and then make the connection, and then merge

CodePudding user response:

The select xuh, (the select wm_concat (name) from t1 where instr (xuh, id) & gt; 0) as the name, the subject from t2

CodePudding user response:

The select xuh, wm_concat (name) the name, the subject
The from (
Select t2 xuh, t1. Id, t1. Name, t2. Subject
The from t2
Join the t1
Where instr (t2) xuh, t1. Id) & gt; 0
The order by t1. Id) tt
Group by xuh, subject;

CodePudding user response:

My Oracle database version does not support wm_concat the function, in addition the instr not line? Now can I give an example of is 1, 2, 3, this kind of treatment, if be 1,12,123 such serial number, have a problem with the instr,

CodePudding user response:

reference 4 floor lixin41658133 response:

my Oracle database version does not support wm_concat the function, in addition the instr not line? Now can I give an example of is 1, 2, 3, this kind of treatment, if be 1,12,123 such serial number, have a problem with the instr,

There will be no problem, wm_contact if not you can download a package, not only with conversion, then concatenate field

CodePudding user response:

reference 4 floor lixin41658133 response:

my Oracle database version does not support wm_concat the function, in addition the instr not line? Now can I give an example of is 1, 2, 3, this kind of treatment, if be 1,12,123 such serial number, have a problem with the instr,

If it is below 9 I, suggest to write a function handle,
If more than 10 g, there will be no problem with this:
The select xuh,
(select wm_concat (name) from t1
Where id in
(select case when instr (xuh, ', ')=0 then xuh else regexp_substr (xuh, '[^,]', 1, level) end
The from dual connect by level<=length (xuh) - length (the replace (xuh, ', ', ')) + 1))
As the name, the subject
The from t2

CodePudding user response:

Design has a problem, a simple complication, but when the job to do or can:
Select SUBSTR (XUHS,
In case the when POS=1 then 1
The else INSTR (XUHS | | ', ', ', ', 1, POS - 1) + 1
End,
Case the when POS=1 then INSTR (XUHS | | ', ', ', ', 1, POS) - 1
The else INSTR (XUHS | | ', ', ', ', 1, POS) - INSTR (XUHS | | ', ', ', ', 1, POS - 1) - 1
End
) XUH, SUBJECT
The from (select XUH xuhs, length (XUH) - length (the replace (XUH, ', ', ')) + 1 NUMS, SUBJECT from T2) a,
(select rownum POS from T1) B
Where p. OS<=a.N UMS
The order by XUH, SUBJECT;