WM_CONCAT duplicates removal

Question

I have a table like below.

   colA       colB
   12345      NHS,CDE,BCD
   12345      NHS,ABC,DEF

Need to display the data in below format

   colA       colB
   12345      NHS,ABC,BCD,CDE,DEF

I need generalized solution to remove the duplicate NHS, which comes first, and then put the remaining words in alphabetical order.

Answer 1

Firstly, never use WM_CONCAT since it is undocumented and is no more available in the latest version 12c. See Why not use WM_CONCAT function in Oracle? and Why does the wm_concat not work here?

Since you are on 11g, you could use LISTAGG.

The below query does following things:

1. Split the comma delimited string into rows.
2. Apply string aggregation using LISTAGG.
3. CASE expression to handle the custom ordering.

For example,

SQL> WITH DATA AS(
  2      SELECT 12345 colA, 'NHS,CDE,BCD' colB FROM dual UNION ALL
  3      SELECT 12345 colA, 'NHS,ABC,DEF' colB FROM dual
  4      )
  5  SELECT cola,
  6    listagg(colb, ',') WITHIN GROUP(
  7  ORDER BY
  8    CASE colb
  9      WHEN 'NHS'
 10      THEN 1
 11      ELSE 2
 12    END, colb) colb
 13  FROM
 14    (SELECT DISTINCT cola,
 15      trim(regexp_substr(colb, '[^,]+', 1, LEVEL)) colb
 16    FROM DATA
 17      CONNECT BY LEVEL <= regexp_count(colb, ',')+1
 18    ORDER BY colb
 19    )
 20  GROUP BY cola
 21  /

      COLA COLB
---------- ------------------------------
     12345 NHS,ABC,BCD,CDE,DEF

Edit As @AlexPoole pointed out, explicit ordering was missing and previous query(see edit history) relied on the distinct ordering of the values.