Oracle 11g : comment puis-je regrouper les valeurs de deux colonnes différentes ?

Question

J'ai un oracle VIEW qui contient certaines valeurs dans deux colonnes. Je veux regrouper ces valeurs dans une nouvelle colonne en ajoutant la concatination :

myView : 

---------------------------------
ID  | col 1       | col 2        |
---------------------------------
1   | 1,2,3,4     |V1,V2,V3,V4
2   | 4,5,6,7     |V5,V6,V7,V8

Je veux créer une nouvelle vue en ajoutant une nouvelle colonne col 3 comme ceci :

 ------------------------------------------------------
    ID  | col 1       | col 2      |col 3
 ------------------------------------------------------
    1   | 1,2,3,4     |V1,V2,V3,V4 |1,V1 2,V2 3,V3 4,V4
    2   | 5,6,7,8     |V5,V6,V7,V8 |5,V5 6,V6 7,V7 8,V8

Merci d'avance pour toute aide

Answer 1

Une autre option serait d'utiliser une fonction pl/sql personnalisée.

CREATE OR REPLACE FUNCTION "STR_TO_TABLE" 
  (in_strt   in varchar2,
   in_delim in varchar2 default ',')
  return      str_table
as
  l_str  clob default in_strt || in_delim;
  l_n    number;
  l_data str_table := str_table();
begin
  loop
      l_n := instr( l_str, in_delim );
      exit when (nvl(l_n,0) = 0);
      l_data.extend;
      l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
      l_str := substr( l_str, l_n+length(in_delim) );
  end loop;
  return l_data;
end;

/

create or replace function custom_concat(col1 varchar2,col2 varchar2) 
return varchar2
is
conct_val varchar2(4000);
begin
  select listagg(final, ' ') within group (order by 1) into conct_val from (
  select r1,M.column_value mcv,s.column_value scv,s.s1,M.column_value||','||s.column_value final from (select rownum r1,column_value from table(STR_TO_TABLE(col1))) M join (select rownum s1,column_value from table(STR_TO_TABLE(col2))s1) s on (r1=s1) );
return conct_val;
end;
/

Puis utilisez-le comme ceci -

select col1,col2,custom_concat(col1,col2) from temp_123;

Résultat -

Answer 2

Comme l'a demandé Matthew, une version compatible avec 11gR2 de l'explosion des listes séparées par des virgules, en utilisant les mêmes données d'exemple étendues :

with input_data ( id, col1, col2 ) as (
SELECT 1   , '1,2,3,4',     'V1,V2,V3,V4' from dual union all
SELECT 2   , '4,5,6,7',     'V5,V6,V7,V8' from dual union all
SELECT 3   , 'A',           'VA,VB,VC,VD' from dual union all
SELECT 4   , 'E,F,G',       'VE' from dual union all
SELECT 5   , 'H,I',         '' from dual union all
SELECT 6   , '',            'J,K' from dual
)
, cte (id, col1, col2, pos, combined_value) as (
  select id, col1, col2, level,
    regexp_substr(col1, '(.*?)(,|$)', 1, level, null, 1)
      ||','|| regexp_substr(col2, '(.*?)(,|$)', 1, level, null, 1)
  from input_data
  connect by id = prior id
  and prior dbms_random.value is not null
  and level <= greatest(nvl(regexp_count(col1, ','), 0),
    nvl(regexp_count(col2, ','), 0)) + 1
)
select id,
       col1,
       col2,
       listagg(combined_value, ' ') within group (order by pos) as col3
from cte
group by id, col1, col2;

        ID COL1    COL2        COL3                          
---------- ------- ----------- ------------------------------
         1 1,2,3,4 V1,V2,V3,V4 1,V1 2,V2 3,V3 4,V4           
         2 4,5,6,7 V5,V6,V7,V8 4,V5 5,V6 6,V7 7,V8           
         3 A       VA,VB,VC,VD A,VA ,VB ,VC ,VD              
         4 E,F,G   VE          E,VE F, G,                    
         5 H,I                 H, I,                         
         6         J,K         ,J ,K                         

Les CTE supplémentaires convertissent les valeurs des colonnes appropriées en une liste de nombres séparés, concaténés ensemble pour chaque ID et position dans la liste. Et comme dans la réponse de Matthew, toutes les valeurs concaténées pour chaque ID sont agrégées dans une chaîne séparée par des espaces.

---

Cependant, il serait toujours plus simple et probablement plus efficace de revenir à la source de la vue actuelle - en supposant qu'elle crée elle-même le fichier col1 y col2 par le biais d'une agrégation de chaînes de caractères - et baser votre nouvelle requête/vue sur cette requête originale.

La construction de vues au-dessus d'autres vues peut entraîner des problèmes de performances, car l'optimiseur ne peut pas toujours faire passer les prédicats au bon endroit. Mais créer des listes agrégées de valeurs, les diviser, puis les ré-agréger, c'est faire plus de travail que nécessaire.

Answer 3

Vous devez diviser les données séparées par des virgules. col1 y col2 en rangées, puis concatène chaque rangée, et enfin ramène les concaténations en une seule par chaîne séparée par des virgules.

La division est faite en utilisant l'astuce bien connue de l'utilisation de CONNECT BY pour générer une ligne "factice" par entrée dans la liste, puis en utilisant la fonction REGEXP_SUBSTR pour sélectionner chaque valeur séparée par des virgules.

L'enroulement à la fin se fait via LISTAGG .

Voici l'ensemble (avec des données de test supplémentaires pour tenir compte des disparités dans le nombre d'éléments de chaque colonne) :

with input_data ( id, col1, col2 ) as (
SELECT 1   , '1,2,3,4',     'V1,V2,V3,V4' from dual union all
SELECT 2   , '4,5,6,7',     'V5,V6,V7,V8' from dual union all
SELECT 3   , 'A',           'VA,VB,VC,VD' from dual union all
SELECT 4   , 'E,F,G',       'VE' from dual union all
SELECT 5   , 'H,I',         '' from dual union all
SELECT 6   , '',            'J,K' from dual
)
select i.id,
       i.col1,
       i.col2,
       listagg(trim(regexp_substr(i.col1, '[^,]+', 1, p.pos)) ||
          ',' || trim(regexp_substr(i.col2, '[^,]+', 1, p.pos)),',') 
          within group ( order by p.pos ) col3
from input_data i
cross apply ( select rownum pos 
              FROM dual 
              connect by level <= 
                        greatest(nvl(regexp_count(i.col1,','),0),
                                 nvl(regexp_count(i.col2,','),0)) +1 ) p
group by i.id, i.col1, i.col2;

Résultats :

+----+---------+-------------+---------------------+
| ID |  COL1   |    COL2     |        COL3         |
+----+---------+-------------+---------------------+
|  1 | 1,2,3,4 | V1,V2,V3,V4 | 1,V1,2,V2,3,V3,4,V4 |
|  2 | 4,5,6,7 | V5,V6,V7,V8 | 4,V5,5,V6,6,V7,7,V8 |
|  3 | A       | VA,VB,VC,VD | A,VA,,VB,,VC,,VD    |
|  4 | E,F,G   | VE          | E,VE,F,,G,          |
|  5 | H,I     |             | H,,I,               |
|  6 |         | J,K         | ,J,,K               |
+----+---------+-------------+---------------------+

Answer 4

Vous devez le faire en PLSQL (ou une requête sql plus importante) pour chaque ligne :

1. Diviser col1 par le délimiteur et sauvegarder dans un tableau
2. Diviser col2 par un délimiteur et sauvegarder dans un tableau
3. pour i=0 analyser le tableau et le concaténer à une chaîne de caractères array1[i] || ',' ||array2[i] || ' '
4. Insérer la chaîne de résultats dans la col3

C'est ainsi que se fait la méthode de fractionnement :

SELECT num_value
      FROM (SELECT   TRIM (REGEXP_SUBSTR (num_csv, '[^,]+', 1, LEVEL)) num_value
              FROM   ( SELECT   col1 num_csv FROM table_view)
              CONNECT BY   LEVEL <= regexp_count (num_csv, ',', 1) + 1)