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
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>