Oracle多行数据转成一行

雨点打透心脏的1/2处 2022-03-21 04:18 545阅读 0赞

由于需要，需要将获取到的机构组织下的子组织组成一行数据，使用到了Oracle自带的函数  
**listagg (列名,‘分隔符’) within group(order by 列名)**  
即在每个分组内，LISTAGG根据order by子句对列植进行排序，将排序后的结果拼接起来

我的组织结构是个树形结构，下面的SQL则是查询出当前部门下的所有子部门的名称。

SELECT LISTAGG(O.ORGNAME,',') WITHIN GROUP(ORDER BY LEVEL)
    FROM ORGANIZATION O
    START WITH O.ORGID = 1000 CONNECT BY PRIOR O.ORGID = O.PID AND LEVEL<4

> 注：上面的SQL用到了Oracle的关键字LEVEL，它表示了树形结构中的数据在哪个层级  
> 上面SQL还用到了Oracle树形查询语句START WITH … CONNECT BY PRIOR …  
> 若查询的是该节点的所有父节点，则上面START WITH SQL应该改为：  
> `START WITH O.ORGID = 1000 CONNECT BY PRIOR O.PID = O.ORGID` (PRIOR后面的字段与先前的顺序不一样）

ORGANIZATION表数据如下

<table> 
 <thead> 
  <tr> 
   <th>ORGID</th> 
   <th>ORGNAME</th> 
   <th>PID</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1000</td> 
   <td>小学</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>1100</td> 
   <td>一年级</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>1200</td> 
   <td>二年级</td> 
   <td>1000</td> 
  </tr> 
  <tr> 
   <td>1101</td> 
   <td>101班</td> 
   <td>1100</td> 
  </tr> 
  <tr> 
   <td>1102</td> 
   <td>102班</td> 
   <td>1100</td> 
  </tr> 
  <tr> 
   <td>1103</td> 
   <td>103班</td> 
   <td>1100</td> 
  </tr> 
  <tr> 
   <td>1201</td> 
   <td>201班</td> 
   <td>1200</td> 
  </tr> 
 </tbody> 
</table>

1.LEVEL

SELECT ORGNAME,LEVEL FROM ORGANIZATION 
    START WITH ORGID = 1000 CONNECT BY PRIOR O.ORGID = O.PID

执行结果如下

<table> 
 <thead> 
  <tr> 
   <th>ORGNAME</th> 
   <th>LEVEL</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>小学</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>一年级</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>二年级</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>101班</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>102班</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>103班</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>201班</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

2.LISTAGG…WITHIN GROUP…

SELECT LISTAGG(O.ORGNAME,',') WITHIN GROUP(ORDER BY LEVEL)
    FROM ORGANIZATION O

执行结果如下：  
小学，一年级，二年级，101班，102班，103班，201班

3.将父节点下的所有子节点拼成一行

SELECT LISTAGG(O.ORGNAME,',') WITHIN GROUP(ORDER BY LEVEL)
    FROM ORGANIZATION O
    START WITH O.ORGID = 1000 CONNECT BY PRIOR O.ORGID = O.PID AND LEVEL<4

执行结果如下：  
小学，一年级，二年级，101班，102班，103班，201班