Accumulation de données dans une même colonne [Résolu]

[Nico820]

Bonjour à tous,

je fais des regroupement de tables avec des left joins.
Malheureusement, lorsque une ligne de la table A a deux ou plus références avec la table B, la table A+B contient toutes les référence.
Vu qu'il existe des quantités dans la table A, je ne voudrais pas que ces quantités se répètent dans la table résultante A+B.
Par conséquent, je voudrais définir dans la table A+B une nouvelle colonne qui rassemble ("concat") toutes les références d'une même ligne.

Si personne n'a rien compris, alors un exemple expliquerait tout:

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
 
TABLE A:
ID  Nom_Personne
1   Pierre
2   Marie
 
TABLE B:
ID  Club        Prix
1   Foot        10 euros
2   Tennis      20 euros
 
TABLE "Link"
ID  Ref_Persone  Ref_Club
1   1                 1  (càd Pierre -> club foot)
2   1                 2
3   2                 2
 
Résultat TABLE A+B avec la TABLE "link":
Nom          Club         Participation 
Pierre        Foot         10 euros
Pierre        Tennis       20 euros
Marie        Tennis       20 euros
 
Résultat souhaité
Nom         Club                      Participation
Pierre       'Foot et tennis'        30 euros
Marie       Tennis                    20 euros

Ainsi, les lignes ne se répètent pas

Remarque: J'ai fait un exemple simple exprès, faut pas comprendre le sens des tables, mais leur logique.

Merci d'avance,

Nicolas

[SQLpro]

Pour cela vous devez utiliser une requête récursive avec les CTE.
Lisez l'article que j'ai écrit à ce sujet; il y a de nombreux exemples qui répondent parfaitement à votre problème :
http://sqlpro.developpez.com/cours/s...te-recursives/

A +

[Nico820]

super merci

[Nico820]

J'ai lu votre cours plusieurs fois, mais en vain.
Pourriez-vous donner un exemple simple avec l'exemple ci-dessus??
C'est sourtout l'addition de caractères ("club"[1] + ' et ' + "club"[2] => "Foot et tennis") que je dois faire.

[aieeeuuuuu]

Bonjour,

Voici un exemple à partir de vos données, que j'ai un enrichies...

les différents club sont séparés par des virgules, sauf les deux derniers, séparés par "et"

Code SQL :

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
67
68
 
;WITH A AS ( --jeu d'essai
	SELECT 1 AS ID, 'Pierre' AS Nom_Personne
	UNION ALL 
	SELECT 2, 'Marie'
)
,B AS ( --jeu d'essai
	SELECT 1 AS ID, 'Foot' AS Club, 10 AS Prix
	UNION ALL
	SELECT 2, 'Tennis', 20
	UNION ALL
	SELECT 3, 'badminton', 55
)
,Link AS ( --jeu d'essai
	SELECT 1 AS ID, 1 AS Ref_Personne, 1 AS Ref_Club
	UNION ALL
	SELECT 2,1,2
	UNION ALL
	SELECT 3, 2, 2
	UNION ALL 
	SELECT 4, 1, 3
)
, LinkRN AS (
	SELECT 
		ID, 
		Ref_Personne, 
		Ref_Club, 
		ROW_NUMBER() OVER (PARTITION BY Ref_Personne ORDER BY Ref_Club) AS RN, 
		COUNT(*) OVER (PARTITION BY Ref_Personne) AS CNT
	FROM Link
)
, CTE AS (
	SELECT 
		Ref_Personne, 
		CAST(B.Club AS VARCHAR(8000)) AS Club, 
		B.Prix, 
		1 AS Rang, 
		CNT AS Last
	FROM LinkRN L
	INNER JOIN B 
		ON L.Ref_Club = B.ID
	WHERE RN = 1
 
	UNION ALL
 
	SELECT 
		CTE.Ref_Personne, 
		CASE 
			WHEN L.RN = L.CNT 
			THEN CTE.Club + ' et ' + B.Club
			ELSE CTE.Club + ', ' + B.Club
		END, 
		CTE.Prix + B.Prix,
		Rang + 1,
		CNT
	FROM CTE
	INNER JOIN LinkRN L
		ON L.Ref_Personne = CTE.Ref_Personne
		AND L.RN = Rang + 1
	INNER JOIN B 
		ON L.Ref_Club = B.ID
)
SELECT A.Nom_Personne, CTE.Club, Prix
FROM A
LEFT OUTER JOIN CTE
	ON A.ID = CTE.Ref_Personne
WHERE Rang = Last
ORDER BY Nom_Personne

[Waldar]

Une autre solution, plus nébuleuse, mais plus courte.
Par contre elle ne change pas le dernier séparateur en 'et', et est extrêmement spécifique à SQL-Server :

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
;WITH A (ID, Nom_Personne) AS
(
SELECT 1, 'Pierre' UNION ALL 
SELECT 2, 'Marie'
)
   ,  B (ID, Club, Prix) AS
(
SELECT 1, 'Foot'     , 10 UNION ALL
SELECT 2, 'Tennis'   , 20 UNION ALL
SELECT 3, 'Badminton', 55
)
   ,  Link (ID, Ref_Personne, Ref_Club) AS
(
SELECT 1, 1, 1 UNION ALL
SELECT 2, 1, 2 UNION ALL
SELECT 3, 2, 2 UNION ALL 
SELECT 4, 1, 3
)
  SELECT A.Nom_Personne
       , LEFT(D.ClubNames, len(D.ClubNames)-1) AS ClubNames
       , sum(B.Prix) AS Prix
    FROM A
         INNER JOIN Link AS L
           ON L.Ref_Personne = A.ID
         INNER JOIN B
           ON B.ID = L.Ref_Club
         CROSS APPLY ( SELECT B2.Club + ', '
                         FROM Link AS L2
                              INNER JOIN B AS B2
                                ON B2.ID = L2.Ref_Club
                        WHERE L2.Ref_Personne = A.ID
                     ORDER BY B2.Club
                      FOR XML PATH('') )  D ( ClubNames )
GROUP BY A.Nom_Personne
       , D.ClubNames
ORDER BY A.Nom_Personne ASC
 
Nom_Personne ClubNames                 Prix
------------ ------------------------- -----------
Marie        Tennis                    20
Pierre       Badminton, Foot, Tennis   85

[Nico820]

Super, merci beaucoup pour vos réponses

Existe-t-il une façon plus rapide pour regrouper les données?
Par exemple, le group by, mais avec une concaténation des champs strings, c'est possible?
Les CTE, c'est ce qu'il y a de plus adapté/optimal et s'il existe une autre solution pour éviter trop de gestion/calculs, c'est encore mieux.
En effet, je dois gérer plus de 20 tables interconnectées et adapter les solutions avec la récursivité prendraient plusieurs jours pour un résultat qui a l'air simple.

[Nico820]

Après plein de recherches sur le net ( par exemple: http://www.eggheadcafe.com/software/...-group-by.aspx ), j'ai vu que with + CTE, c'est le mieux

Bref, merci encore pour vos réponses

a+

[mikedavem]

J'ai pu constater par expérience que la méthode à employer dépendant de beaucoup la volumétrie des données à concaténer (et bien entendu des index placés)

Par exemple l'emploi des CTE récursives étaient beaucoup mois performantes que la concaténation via les méthodes XPATH lorsque j'arriverais dans mon cas à plus de 100 000 lignes.

Je pense qu'il est important pour vous de prendre ceci en considération.

++

[Waldar]

Je rejoins mikedavem sur ce point, "mieux" est dépendant des personnes et des environnements.

Certains préféreront avoir une UDF plus lente mais plus simple d'utilisation, d'autres préféreront la syntaxe normative à base de CTE, d'autres préféreront la solution plus rapide avec XML Path.

Ça dépend vraiment de vos contraintes et de ce que vous êtes prêt à coder.