Department of ORACLE table dynamic transformation-CodePudding

There are department level surface is not fixed, as follows:

Now need to function or the query into the following format:

To ask what to do

CodePudding user response:

 
The select bmid parent_bmid, substr (sysjbmid, 0, instr (sysjbmid, ', ') - 1) leave1, 
Substr (sysjbmid, instr (sysjbmid, ', ') + 1, instr (sysjbmid, ', ', 2) - 1) leave2, 
Substr (sysjbmid, instr (sysjbmid, ', ', 2) + 1, instr (sysjbmid, ', ', 3) - 1) leave3, 
Substr (sysjbmid, instr (sysjbmid, ', ', 3) + 1, instr (sysjbmid, ', ', 4) - 1) leave4 
The from tab_name

Well probably similar to intercept, as to how many columns, dynamic statement used to splice

CodePudding user response:

reference 1st floor js14982 response:

 
The select bmid parent_bmid, substr (sysjbmid, 0, instr (sysjbmid, ', ') - 1) leave1, 
Substr (sysjbmid, instr (sysjbmid, ', ') + 1, instr (sysjbmid, ', ', 2) - 1) leave2, 
Substr (sysjbmid, instr (sysjbmid, ', ', 2) + 1, instr (sysjbmid, ', ', 3) - 1) leave3, 
Substr (sysjbmid, instr (sysjbmid, ', ', 3) + 1, instr (sysjbmid, ', ', 4) - 1) leave4 
The from tab_name

Well probably similar to intercept, as to how many columns, dynamic statement used to splice

less, to put in the name of the department, not the id...

CodePudding user response:

refer to the second floor AcceleL7 response:

Quote: refer to 1st floor js14982 response:

The select bmid parent_bmid, substr (sysjbmid, 0, instr (sysjbmid, ', ') - 1) leave1,
Substr (sysjbmid, instr (sysjbmid, ', ') + 1, instr (sysjbmid, ', ', 2) - 1) leave2,
Substr (sysjbmid, instr (sysjbmid, ', ', 2) + 1, instr (sysjbmid, ', ', 3) - 1) leave3,
Substr (sysjbmid, instr (sysjbmid, ', ', 3) + 1, instr (sysjbmid, ', ', 4) - 1) leave4
The from tab_name

Well probably similar to intercept, as to how many columns, dynamic statement used to splice

less, to put in the name of the department, not the id...

this yourself around

CodePudding user response:

The select bmid parent_bmid,
(select BMMC from tab_name where bmid=substr (t.s ysjbmid, 0, instr (t.s ysjbmid, ', ') - 1)) leave1
.
The from tab_name t

CodePudding user response:

Sys_connect_by_path to brush it again