Tuesday, September 18, 2018

PLSQL CONCAT multiple rows in one string


     
       select CONCAT(REPLACE(listagg( ENTITY_NAME, chr(300)||'(') within group( order by NAME_TYPE_FLG DESC),',',' ')  ,')')AS X
 FROM  CISADM.CI_PER_NAME PN
            WHERE PN.PER_ID ='2252300000'
           AND  PN.NAME_TYPE_FLG IN(   'ALT' , 'PRIM')


output

another solution

SELECT wm_concat('('||ENTITY_NAME||')') 
 FROM
        (   SELECT PER_ID, PN.ENTITY_NAME
            FROM  CISADM.CI_PER_NAME PN
            WHERE PN.PER_ID ='2252300000'
           AND  PN.NAME_TYPE_FLG IN(   'ALT' , 'PRIM')
           ORDER BY PN.NAME_TYPE_FLG DESC
       )
       GROUP BY PER_ID ;


No comments:

Post a Comment