Ordre d'aggrégation des chaînes [Résolu]

[Zugg]

Bonjour,

J'ai un problème d'ordre lorsque je fais de l'aggrégation de valeurs.
Voici un exemple de données :

Code :

1
2
3
4
5
SELECT 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code FROM dual union ALL
SELECT 'USER0002',NULL,NULL,NULL,NULL,NULL FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' FROM dual

USER1000 2 Info 2 30/08/2011 12:13:11 U-0013 A
USER0002
USER1111 1 Info 1 30/08/2011 12:20:07 U-1703 A
USER1111 1 Info 1 30/08/2011 12:20:23 U-1902 B
USER1111 1 Info 1 30/08/2011 12:22:10 U-2026 A

Je souhaite grouper les valeurs suivant USR, GRP et INFO et concaténer les NOM et concaténer les CODE

voici un exemple de requête pour grouper et concaténer les valeurs :

Code :

1
2
3
4
5
6
7
8
9
10
11
WITH q AS (
SELECT 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code FROM dual union ALL
SELECT 'USER0002',NULL,NULL,NULL,NULL,NULL FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' FROM dual)
SELECT USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       wm_concat(NOM) NOMS,
       wm_concat(CODE) CODES
 FROM q
GROUP BY USR, GRP, INFO

Et en résultat j'obtiens :

USER0002
USER1000 2 Info 2 30/08/2011 12:13:11 U-0013 A
USER1111 1 Info 1 30/08/2011 12:20:07 U-1703,U-2026,U-1902 A,B,A

Le problème c'est que pour le USER1111, je veux les noms dans l'ordre et les codes associés dans l'ordre : U-1703,U-1902,U-2026 et en code : A,B,A, car les 2 infos sont liées et il faut qu'elles soient dans le même ordre, dans l'ordre de la DateHeure.

Je suis sous Oracle 10.2.0.4.0, et j'ai essayé sous Oracle 11.2.0.1.0 et l'ordre de concaténation change entre les versions.
J'ai essayé de mettre un order by :

Code :

1
2
3
4
5
6
7
8
9
10
11
WITH q AS (
SELECT 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code FROM dual union ALL
SELECT 'USER0002',NULL,NULL,NULL,NULL,NULL FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' FROM dual)
SELECT USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       wm_concat(NOM) NOMS,
       wm_concat(CODE) CODES
FROM (SELECT * FROM q ORDER BY USR, GRP, INFO, DATEHEURE)
GROUP BY USR, GRP, INFO

Mais ça ne change rien.

Comment faire ?

Merci

[mnitu]

wm_concat/stragg ne trie pas.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
WITH q AS (
SELECT 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code FROM dual union ALL
SELECT 'USER0002',NULL,NULL,NULL,NULL,NULL FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' FROM dual)
SELECT USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       cast(collect(NOM ORDER BY dateheure)  AS varchar2_tt) NOMS,
       cast(collect(CODE  ORDER BY dateheure)  AS varchar2_tt) CODES
FROM (SELECT * FROM q ORDER BY USR, GRP, INFO, DATEHEURE)
GROUP BY USR, GRP, INFO

[Zugg]

Oui l'aggrégation ne trie pas en effet, d'où mon problème.

La solution proposée, suppose la création du type :

Code :

CREATE type varchar2_tt AS TABLE of VARCHAR2(100);

Mais au final, j'obtiens pas mes éléments sous forme d'une chaine, comment faire ?

[mnitu]

Vous pouvez trouver une réponse dans "the collect function in 10g"

[Zugg]

merci mnitu, tout est bien expliqué dans ton lien.

Sinon, après avoir tourné la requête dans tous les sens j'ai une autre solution qui ne nécessite aucun type ni fonction :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
WITH q AS (
SELECT 'USER1000' Usr,2 Grp, 'Info 2' Info, '30/08/2011 12:13:11' DateHeure, 'U-0013' Nom, 'A' Code FROM dual union ALL
SELECT 'USER0002',NULL,NULL,NULL,NULL,NULL FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:07', 'U-1703', 'A' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:20:23', 'U-1902', 'B' FROM dual union ALL
SELECT 'USER1111',1, 'Info 1', '30/08/2011 12:22:10', 'U-2026', 'A' FROM dual)
SELECT USR, GRP, INFO, min(DATEHEURE) DATE_MIN,
       max(NOMS) NOMS,
       max(CODES) CODES
FROM (
SELECT  USR, GRP, INFO, DATEHEURE, 
       wm_concat(NOM) over (partition BY USR, GRP ORDER BY USR, GRP, DATEHEURE) NOMS,
       wm_concat(CODE) over (partition BY USR, GRP ORDER BY USR, GRP, DATEHEURE) CODES
       FROM q )
GROUP BY USR, GRP, INFO

à priori ça donne bien le résultat voulu.
merci

[mnitu]

Très bien. Sur le même site: listagg function in 11g release 2

[Zugg]

oui en effet.

Le problème c'est que je dois être compatible minimum Oracle 10gR2.

En tout cas merci pour le lien, je vais le garder de côté.