oracle 显示一行,Oracle多行变成一列一行显示。

查询某个表(如证书表)，里面一个人一个证书就是一行数据，一个人可能有多个证书(即多行)。需要显示为某个人所有证书显示在一行，用标识符隔开。如下：

要得结果为：

SQL语句如下：

SELECT

pk_psndoc,

LISTAGG( to_char(glbdef1), '&') WITHIN GROUP(ORDER BY glbdef1) AS zhengShu,

LISTAGG( to_char(glbdef2), '&') WITHIN GROUP(ORDER BY glbdef2) AS zhengShuhao,

LISTAGG( to_char(glbdef3), '&') WITHIN GROUP(ORDER BY glbdef3) AS zhengShushijian,

LISTAGG( to_char(glbdef4), '&') WITHIN GROUP(ORDER BY glbdef4) AS zhengShujiguan

FROM (

select

distinct

pk_psndoc,

case when glbdef1 is null then '无' else glbdef1 end  glbdef1,

case when glbdef2 is null then '无' else glbdef2 end  glbdef2,

case when glbdef3 is null then '无' else glbdef3 end  glbdef3,

case when glbdef4 is null then '无' else glbdef4 end glbdef4

from hi_psndoc_glbdef1  )

group by pk_psndoc