2

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.

schizoid04
  • 888
  • 1
  • 11
  • 27
Raghava Ch
  • 77
  • 2
  • 10
  • Processing denormalized data to produce denormalized data? Hopefully this is a one time only job. If you actually need this you might consider using arrays instead. – dnoeth May 14 '15 at 07:43

2 Answers2

2

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.

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • Being picky, I'd prefer to see your order by include the actual `colb` value as well as the case: `(ORDER BY CASE colb WHEN 'NHS' THEN 1 ELSE 2 END, colb)` It works now but relies on `distinct` ordering the values, and who knows if that might change one day *8-) – Alex Poole May 14 '15 at 08:50
  • @AlexPoole Good point, thanks. Though the ORDER BY in the inline view won't make much difference here. – Lalit Kumar B May 14 '15 at 09:08
  • No, I agree, I only meant in the listagg's order-by clause. – Alex Poole May 14 '15 at 09:11
  • thanks Lalit and Alex for the clarifications. btw is there any way to remove NHS from order by, so that I can write a generalized query. – Raghava Ch May 14 '15 at 19:19
  • @RaghavaCh, use SUBSTR to extract the first vaue, retain it, aggregate only the other values, finally append them. To understand, execute each subquery in the above solution one by one starting from the innermost one. Look at the resultset, you will get an idea how to generalize the query. – Lalit Kumar B May 15 '15 at 03:24
  • What if I am using oracle 10g? LISTAGG does not work in 10g. Any solution? – Yubaraj Apr 07 '17 at 13:59
  • @Yubaraj You do it like this https://oracle-base.com/articles/misc/string-aggregation-techniques#row_number – Lalit Kumar B Apr 07 '17 at 14:25
  • @LalitKumarB I will look on this. Thank you . – Yubaraj Apr 07 '17 at 14:27
0
WITH t AS
  (SELECT col1,wm_concat(col2) AS col2 FROM test1 GROUP BY col1
  ) , t1 AS
  ( SELECT DISTINCT col1, regexp_substr(col2, '[^,]+', 1, rownum) names
  FROM t
    CONNECT BY rownum <= LENGTH(regexp_replace(col2, '[^,]'))+1
  ORDER BY names
  )
SELECT col1,wm_concat(names) AS names FROM t1 GROUP BY col1
Rajesh Ujade
  • 2,715
  • 19
  • 39
tejadeep
  • 1