Concatener les resultats d'un subquery

Version imprimable

Bonjour.

Je bloque depuis qq temps déjà (je n'ose même pas dire combien :p) sur une petite requete.

Voici mon modèle simplifié :

Code:

1
2
3
4
5
6
7
8
9
10
11
 
                   ----------------    ---------------- 
                   |   AP_USER    |    |    APPL      |   
----------------   ----------------    ----------------   
|    USER      |   |              |    |              |   
----------------   | APPL_ID      | <--| APPL_ID      |   
|              |   |              |    |              |   
| USER_ID      |-->| USER_ID      |    | APPL_NAME    |   
|              |   ________________    ________________   
| USER_NAME    | 
________________

Je désire récupérer un résultat du genre suivant :

Code:

1
2
3
4
5
6
7
8
 
------------------------------
| USER       |    APPL          |
 ------------------------------
| user 1     | app 1,app 2,app 3 |
 ------------------------------
| user 2     | app 1,app 4,app 5 |
 ------------ -----------------

Mais pour l'instant je n'arrive à récupérer que l'ensemble des lignes suivante avec cette requete.

Code:

1
2
3
4
5
6
7
 
select u.user_name, sq.app_name
from user u,
       ap_user au,
  (select app_id, app_name from app) sq
where u.user_id=au.user_id
and au.app_id=sq.app_id

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
 ------------------------------
| USER       |    APPL         |
 ------------------------------
| user 1     | app 1           |
 ------------------------------
| user 1     | app 2           |
 ------------------------------
| user 1     | app 3           |
 ------------------------------
| user 2     | app 1           |
 ------------ -----------------
| user 2     | app 4           |
 ------------ -----------------
| user 2     | app 5           |
 ------------ -----------------

Il faudrait que j'arrive à construire une requete qui me concaténerais la list des app de la sous requete. (qui ici ne sert pas a grand chose)

je bloque completement, si quelqu'un a des pistes pour reussir cela en sachant que cette requete devra être utilisée dans un outil online qui ne permet pas l'utilisation des functions perso ni du PL/SQL :(

Merci d'avance.

23/02/2007, 15h25
laville

Bonjour,

je ne suis pas un expert mais pourquoi tu ne groupes pas par user et ensuite, tu crés une variable APPL as select et tu concatène tes 3 variables where user = user de départ.

En gros t'as deux requetes imbriquées, une pour séléectionner les user et grouper, et la seconde qui te récupère les appl et les concatènes avec comme condition l'user de la première requête.

Après, peut être qu'une autre personne pourra te donner la procédure directement :roll:

Salut, c'est possible avec les types objets (depuis la 9i)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),
 
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,
 
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,
 
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,
 
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS
 
 
CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;
 
  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ', ' || value;
    RETURN ODCIConst.Success;
  END;
 
  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ', '), ', ');
    RETURN ODCIConst.Success;
  END;
 
  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ', ' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS
 
 
CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

Exemple (select basic) :

Citation:

Select
SocialGroup_i "Groupe"
, Requester_i "Membre"
From SocialGroupMember
Where SocialGroup_i = 18407 ;

Groupe Membre

18407 31143
18407 31144
18407 31145
18407 31146
18407 31147
18407 31144

avec la fonction String_agg :

Citation:

Select
SocialGroup_i "Groupe"
, string_agg(Requester_i) "Membre"
From SocialGroupMember
Where SocialGroup_i = 18407
Group by SocialGroup_i;

Groupe Membre

18407 31143, 31144, 31145, 31147, 31144, 31146

Il y a une autre solution que celle de Yorglaa qui marche très bien d'ailleurs ;)

Créer une fonction CONCAT_LIST

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE OR REPLACE FUNCTION CONCAT_LIST(cur SYS_REFCURSOR )
  RETURN  VARCHAR2 IS
    ret VARCHAR2(32000); 
    tmp VARCHAR2(4000);
BEGIN
    LOOP
        FETCH cur INTO tmp;
        EXIT WHEN cur%NOTFOUND;
            ret := ret || ';' || tmp;
    END LOOP;
    RETURN ret;
END CONCAT_LIST;

Que tu appelles ensuite dans ta requete
Nota : la requete ci-jointe est faite à la va vite je l'ai pas testé avec un jeu de données similaire à ton cas

Code:

1
2
3
4
5
6
7
8
9
10
11
    SELECT U2.USER_NAME,
           CONCAT_LIST(CURSOR(
                              SELECT AP1.appl_name
                              FROM USER U1,
                                   AP_USER AU1,
                                   APPL AP1
                              WHERE U1.USER_ID=AU1.USER_ID
                              AND AU1.APPL_ID=AP1.APPL_ID
                              AND U1.USER_ID=U2.USER_ID 
                              )) VALEURS
    FROM USER U2

tiens moi au courant si çà marche ;)