On a parfois besoin de concaténer dans une colonne le contenu de plusieurs lignes d’une table ou d’une requête.
Par exemple, On part de :
pour arriver à :
Je vous propose une fonction écrite en VBA qui réalise cette transposition.
Code de la fonction VBA
' Procédure : ConcatColonne [Function]
' Retour : String
' Auteur : PhilBen - Free to use
' Version : 1.06
' Création/Maj : Samedi 15 septembre 2012
' Objet : Permet de concaténer en lignes les données d'une colonne en fonction d'un pivot
' Arguments : - ValeurPivot : La valeur de la colonne pivot
' : - NomColonnePivot : Nom de la colonne pivot
' : - NomColonneConcat : Nom de la colonne à concaténer
' : - NomDomaine : Table ou requête des colonnes
' : - Filtre : Permet d'appliquer un filtre supplémentaire sur le domaine ("" pour aucun)
' : - RegrouperCompter : Regrouper (=1 ou >0 et 2) ET Compter (=2) ou pas de regroupement (=0) des éléments concaténés
' : - PasVideNULL : ne retourne pas les éléments concaténés vide "" et NULL (=True) sinon False
' : - TriAscendant : Tri Ascendant (=True), Descendant (=False)
' : - Separateur : String qui sépare les éléments concaténés (ex: ",","---",...)
' Remarques : * Cette fonction ralentit sensiblement la requête :
' -> Meilleure performance si au moins la colonne pivot est indexée avec doublons
' : * Le type de la colonne pivot peut être Date, numérique, string
' : * Pour des raisons de performance, éviter de faire un filtre, un tri,
' : un regroupement sur la colonne de concaténation, la déclarer si possible 'Expression' !
' Exemple : SELECT [MaColPivot],
' : ConcatColonne([MaColPivot],"MaColPivot","MaColConcat","MaTable","",1,True,True,", ")
' : FROM MaTable Group By [MaColPivot]
' Historique : 1.03 : Correction bug si le pivot est de type date
' : 1.04 : Complément d'information dans l'en-tête de la fonction
' : 1.05 : Correction bug si Pivot numérique avec décimales (, -> .)
' : 1.06 : Ajout compte des données regroupées + modification des paramètres (type et nom)
'----------------------------------------------------------------------------------------------------------
Public Function ConcatColonne(ByVal ValeurColonnePivot As Variant, _
ByVal NomColonnePivot As String, _
ByVal NomColonneConcat As String, _
ByVal NomDomaine As String, _
Optional ByVal Filtre As String = vbNullString, _
Optional ByVal RegrouperCompter As Integer = 1, _
Optional ByVal PasVideNULL As Boolean = True, _
Optional ByVal TriAscendant As Boolean = True, _
Optional ByVal Separateur As String = ", ") As String
On Error GoTo Catch
Dim oDb As DAO.Database, oRs As DAO.Recordset, sSQL As String
If Not IsNull(ValeurColonnePivot) Then
If RegrouperCompter = 2 Then 'regrouper ET compter
sSQL = NomColonneConcat & " & "" ("" & " & "COUNT(*)" & " & "")"" & " & """" & Separateur & """ As C"
Else
sSQL = NomColonneConcat & " & """ & Separateur & """ As C"
End If
sSQL = "SELECT " & sSQL & " FROM " & NomDomaine & " WHERE " & NomColonnePivot & "="
Select Case VarType(ValeurColonnePivot)
Case vbString
sSQL = sSQL & """" & ValeurColonnePivot & """"
Case vbDate
sSQL = sSQL & Format(ValeurColonnePivot, "\#m-d-yyyy h:n:s\#")
Case Else 'Numériques
sSQL = sSQL & Replace(ValeurColonnePivot, ",", ".")
End Select
If Filtre <> vbNullString Then sSQL = sSQL & " And " & Filtre
If PasVideNULL = True Then sSQL = sSQL & " AND LEN(NZ(" & NomColonneConcat & "))>0"
If RegrouperCompter > 0 Then sSQL = sSQL & " GROUP BY " & NomColonneConcat
sSQL = sSQL & " ORDER BY " & NomColonneConcat
If Not TriAscendant Then sSQL = sSQL & " DESC"
'Lance la requête et concatène les lignes
Set oDb = CurrentDb
Set oRs = oDb.OpenRecordset(sSQL, dbOpenSnapshot)
If Not oRs.EOF Then
Do
ConcatColonne = ConcatColonne & oRs(0)
oRs.MoveNext
Loop Until oRs.EOF
ConcatColonne = Left$(ConcatColonne, Len(ConcatColonne) - Len(Separateur))
End If
oRs.Close
End If
Finally:
Set oRs = Nothing
Set oDb = Nothing
Exit Function
Catch:
ConcatColonne = "Erreur !"
Resume Finally
End Function
Exemples d’utilisation
Un collectionneur de modèles réduits, a noté consciencieusement ses achats de lots de voitures sur une feuille quadrillée. Un ami, utilisateur débutant d’Access, a porté ce document dans une table Access sans connaître les formes normales de Codd !
Voici le code pour créér et peupler partiellement la table dont la structure n’est pas à suivre…
Const cInsert As String = "INSERT INTO tConcat (Marque, Modele, DateAchat, Couleur, Nombre, Prix) VALUES "
With DoCmd
.SetWarnings False
.RunSQL "CREATE TABLE tConcat (Marque VARCHAR, Modele VARCHAR, DateAchat DATE, Couleur VARCHAR, Nombre LONG, Prix DOUBLE);"
.RunSQL "CREATE INDEX IdxMarque ON tConcat (Marque);"
.RunSQL cInsert & "('Citroën','DS',#6/3/1993#,'blanc',2,85.25);"
.RunSQL cInsert & "('Citroën','DS',#2/13/1999#,'blanc',5,405.3);"
.RunSQL cInsert & "('Peugeot','205',#11/18/1998#,'vert',1,31.2);"
.RunSQL cInsert & "('Peugeot','404',#03/25/1978#,'noir',3,NULL);"
.RunSQL cInsert & "('Peugeot','205',#12/07/1997#,'bleu',1,31.2);"
.RunSQL cInsert & "('Peugeot','404',#03/25/1978#,'marron',1,42.1);"
.RunSQL cInsert & "('Renault','R5',#01/28/1982#,'rouge',5,255.5);"
.RunSQL cInsert & "('Renault','R4',#07/11/1991#,'bleu',3,159.9);"
.RunSQL cInsert & "('Renault','R4',#07/11/1991#,'rouge',5,238.75);"
.SetWarnings True
End With
MsgBox "Création terminée"
End Function
Cette table contient 6 colonnes (Marque, Modèle, DateAchat, Couleur des voitures, Nombre de voiture du lot, Prix du lot).
Chaque ligne de la table correspond à l’achat d’un lot de voitures et toutes les voitures d’un lot ont la même couleur.
Le collectionneur souhaite obtenir des infos sur sa collection en concaténant l’information dans une colonne car c’est plus simple à lire…
Quels sont les modèles achetés de la marque Peugeot ?
Comme la colonne pivot ‘Marque’ doit être égale à ‘Peugeot’, on peut écrire la simple requête suivante :
ConcatColonne("peugeot", "marque",
"modele", "tConcat",
"",0,FALSE,TRUE,",")
AS [MODèles]
La valeur pivot est ‘Peugeot’, la colonne pivot (ou de regroupement) est ‘Marque’, la colonne à concaténer est ‘Modele’, la table est ‘tConcat’, la clause Where est vide « », pas de regroupement des lots ayant le même modèle d’où la valeur 0, on retourne les modèles dont le nom est vide ou NULL (False), on souhaite trier par ordre Ascendant les modèles concaténés (True), et le séparateur entre les modèles est une virgule ‘,’.
On obtient donc :
Peugeot | 205,205,404,404
Le collectionneur est satisfait mais il préfère regrouper les modèles communs et ajouter un espace après la ‘,’
La requête devient :
ConcatColonne("peugeot", "marque",
"modele", "tConcat",
"",1,FALSE,TRUE,", ")
AS [MODèles]
Pour regrouper les modèles, il suffit de passer à 1 au lieu de 0 le paramètre ‘RegrouperCompter’. De plus, le séparateur devient ‘, ‘ au lieu de ‘,’.
On obtient alors :
Peugeot | 205, 404
Il est satisfait mais il perd une information importante qui est le nombre de lots achetés et finalement il se demande si le séparateur ‘ / ‘ ne serait pas mieux…
La solution devient :
ConcatColonne("peugeot", "marque",
"modele", "tConcat",
"",2,FALSE,TRUE," / ")
AS [MODèles]
Pour afficher le compte par modèle, on passe la valeur du paramètre ‘RegrouperCompter’ à 2, et le séparateur est modifié.
Résultat :
Peugeot | 205 (2) / 404 (2)
Qu’elles sont les dates d’achat de ces lots du plus récent au plus ancien ?
ConcatColonne("peugeot", "marque",
"dateachat", "tConcat",
"",2,FALSE,FALSE," ; ")
AS [Dates achat]
La colonne à concaténer devient ‘DateAchat’ et le paramètre ‘TriAscendant’ devient ‘False’.
Résultat :
Peugeot | 18/11/1998 (1) ; 07/12/1997 (1) ; 25/03/1978 (2)
Quels sont les prix d’achat de ces lots ?
ConcatColonne("peugeot", "marque",
"prix" , "tConcat",
"",2,FALSE,FALSE," ; ")
AS [Prix]
On obtient :
Peugeot | 42,1 (1) ; 31,2 (2) ; (1)
On remarque que le dernier compte (1) est sans valeur. En effet, un prix n’était pas renseigné dans le document…
Le collectionneur me dit alors : ‘Cachez-moi cette erreur que je ne saurais voir…’
La requête devient donc :
ConcatColonne("peugeot", "marque",
"prix" , "tConcat",
"",2,TRUE,FALSE," ; ")
AS [Prix]
Il suffit de mettre à ‘True’ le paramètre ‘PasVideNULL’.
On obtient finalement :
Peugeot | 42,1 (1) ; 31,2 (2)
Quels sont les prix d’achat, toutes marques confondues ?
Il est normalement nécessaire de renseigner la valeur et la colonne du pivot…
La solution consiste à mettre 0 pour ‘ValeurColonnePivot’ et ‘0’ pour ‘NomColonnePivot’
La requête devient :
ConcatColonne(0,"0","prix", "tConcat",
"",2,TRUE,FALSE," ; ")
AS [Prix]
et le résultat :
Toutes | 405,3 (1) ; 255,5 (1) ; 238,75 (1) ; 159,9 (1) ; 85,25 (1) ; 42,1 (1) ; 31,2 (2)
Ca serait possible d’avoir tous les prix unitaires ?
Requête :
ConcatColonne(0,"0","[prix]/[nombre]", "tConcat",
"",2,TRUE,FALSE," - ")
AS [Prix]
On déclare que la colonne concaténée est la division du prix sur le nombre de voiture du lot.
Résultat :
Toutes | 81,06 - 53,3 - 51,1 - 47,75 - 42,625 - 42,1 - 31,2
Quels sont les modèles de chaque marque ?
Cette fois-ci, il faut utiliser la clause FROM de la requête pour passer en revue l’ensemble des marques :
ConcatColonne([marque],"marque","modele","tConcat","",1,TRUE,TRUE," - ") AS [MODèles par marque]
FROM tconcat
GROUP BY marque
ORDER BY marque
Résultat :
Citroën | DS
Peugeot | 205 - 404
Renault | R4 - R5
Quels sont les marques et modèles pour chaque couleur ?
ConcatColonne([couleur],"couleur","marque & "" "" & modele","tConcat","",1,FALSE,TRUE," - ")
AS [Marque et MODèle]
FROM tconcat
GROUP BY Couleur
ORDER BY Couleur
Résultat :
blanc | Citroën DS
bleu | Peugeot 205 - Renault R4
marron | Peugeot 404
noir | Peugeot 404
rouge | Renault R4 - Renault R5
vert | Peugeot 205
Puis-je avoir une présentation inversée ?
ConcatColonne([marque] & " " & [Modele],"[Marque] & "" "" & [modele]","couleur","tConcat","",1,FALSE,TRUE," - ")
AS [Couleur]
FROM tconcat
GROUP BY [Marque] & " " & [modele]
ORDER BY [Marque] & " " & [modele]
qui donne :
Citroën DS | blanc
Peugeot 205 | bleu - vert
Peugeot 404 | marron - noir
Renault R4 | bleu - rouge
Renault R5 | rouge
Enfin, je souhaite afficher séparément les colonnes ‘Marque’ et ‘Modèle’ et ne pas voir les informations pour ‘Citroën’
La requête :
Modele,
ConcatColonne([Marque] & " " & [Modele],
"[Marque] & "" "" & [modele]",
"couleur",
"tConcat",
"Marque <> 'citroën'",
1,
FALSE,
TRUE,
" - "
)
AS [Couleur]
FROM tconcat
GROUP BY Marque,
Modele
HAVING Marque <> 'Citroën'
ORDER BY Marque,
Modele
On remarquera l’utilisation du paramètre ‘Filtre’ de la fonction ConcatColonne pour écarter la marque ‘Citroën’ de la concaténation. Dans ce cas précis, l’utilisation du filtre n’est pas indispensable puisque la requête écarte elle-même cette marque (voir clause HAVING).
Résultat attendu :
Peugeot | 205 | bleu - vert
Peugeot | 404 | marron - noir
Renault | R4 | bleu - rouge
Renault | R5 | rouge
Performance
La fonction (et la requête sous-jacente!) est exécutée pour chaque ligne de la table ce qui ralentit sensiblement la requête principale.
Il faudra donc limiter son usage à une table ou requête peu peuplée (< 10 000 lignes ?) et si possible, indexer avec doublons la colonne pivot.
De plus, pour ne pas dégrader les performances, éviter de réaliser des opérations complémentaires sur la colonne concaténée (Where, Order by, Group by) dans la requête principale.
Lien
Voir ce billet pour réaliser une transposition inverse.
@+
Philippe