removing delimiters for nulls in concataned string oracle

April 18, 2024

I have table like below. I would like to concat the columns as all_cells like ABC27_0.5,XYZ22_0.7,DDD_0.99 for first grid and FFF33_0.7 for second grid . How can I achieve that I got millions of records like this.

grid                top1_cell   top1_ratio  top2_cell   top2_ratio top3_cell   top3_ratio
666666666666666666 ABC27         0.5        XYZ22        0.7        DDD        0.99
77777777777777777  FFF33         0.7

I tried the below code but I dont want empty columns included. For example for the second grid I don’t want something like FFF33_0.7,_,_ I only want FFF33_0.7. How can I achieve that ?


SELECT X.*,
RTRIM(XMLAGG(XMLELEMENT(E,TOP_1_CELL||'_'||TOP_1_RATIO,',',TOP_2_CELL||'_'||TOP_2_RATIO,',',TOP_3_CELL||'_'||TOP_3_RATIO).EXTRACT('//text()') ORDER BY TOP_1_CELL,TOP_2_CELL,TOP_3_CELL ).GetClobVal(),',')  AS all_Cells
FROM X
GROUP BY grid

>Solution :

How about pure concatenation which is then prettified?

Sample data:

SQL> select * From test;

      GRID TOP1_CELL  TOP1_RATIO TOP2_CELL  TOP2_RATIO TOP3_CELL  TOP3_RATIO
---------- ---------- ---------- ---------- ---------- ---------- ----------
       666 ABC27              .5 XYZ22              .7 DDD               .99
       777 FFF33              .7
       888                       DEF13              .2

Query: concatenate values, remove superfluous commas:

SQL> with temp as
  2  (
  3  select grid,
  4    top1_cell || case when top1_ratio is not null then '_' || to_char(top1_ratio, 'fm0.0') end ||','||
  5    top2_cell || case when top2_ratio is not null then '_' || to_char(top2_ratio, 'fm0.0') end ||','||
  6    top3_cell || case when top3_ratio is not null then '_' || to_char(top3_ratio, 'fm0.0') end result
  7  from test
  8  )
  9  select grid,
 10         trim(both ',' from result) as result
 11  from temp;

      GRID RESULT
---------- ------------------------------
       666 ABC27_0.5,XYZ22_0.7,DDD_1.0
       777 FFF33_0.7
       888 DEF13_0.2

SQL>