[AC-2007] SQL : mettre en lignes des colonnes [Résolu]

[terence-hill]

Bonjour,

J'ai un soucis donc je ne trouve de solution sur les forums, où alors je m'y prend mal à tenter les requêtes.
Voilà je suis sous Access 2007 et j'ai hérité d'une base de données comme suit :
Table 1 :

Identifiant livre | auteur 1 | auteur 2 | ..... | auteur n |
livre 1 | john | jean | ...... | elise |
livre 2 | mick | rien | ..... | toujours rien |
.........

J'ai créé une table avec les livres :

Table 2 :

identifiant livre | année | nombre page | éditeur |...
livre 1 | 2010 | 654 | edit1 |....
livre 2 | 2001 | 123 | edit2 |...
.....

Et une table avec les auteurs :

Table 3 :

Identifiant auteur | nom | prénom | adresse |...
id 1 |malkovitch |john | USA |...
id 2 | gossini | rené | belgique |
....

j'aimerais donc créer une table reliant livre et auteur comme suit :

Table 4 :

Id livre | Id auteur | rang de l'auteur | nombre d'auteurs total|
livre 1 | id 1 | 5 | 6 |
livre 1 | id 2 | 6 | 6 |
....

Il y a plusieurs auteurs pour un livre donc. Mais pour créer cette troisième table il m'est apparu nécessaire de mettre ma Table 1 en lignes plutôt qu' en colonnes, à savoir :

Table 5 :

Identifiant livre | auteur | rang auteur |
livre 1 | john | 1 |
ivre 1 | jean | 2 |
livre 1 | ..... | ......... |
livre 2 |mick | 1 |

Tout en gardant l'ordre des auteurs je pourrais obtenir le nombre d'auteurs pour chaque livre.
Mais jusqu'à présent tout mes efforts sont vains . Je viens d'essayer une méthode avec UNPIVOT mais soit ma requête est fausse soit Access 2007 ne connait pas cette fonction (je penche plutôt pour la première réponse).
J'en viens donc à vous demander de l'aide. Comment je puis mettre ma
Table 1 en lignes plutôt qu'en colonnes pour obtenir ma Table 5?

Merci d'avance pour les réponses.

Terence-hill

[terence-hill]

J'ai essayé un peu tout et n'importe quoi et j'en suis rendu a faire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT idlivre, [Auteurs 1], IIF([Auteurs 1] Is Not Null,1,Null) as rang 
  FROM Table1
UNION All
SELECT idlivre, [Auteurs 2], IIF([Auteurs 2] Is Not Null,2,Null) as rang
  FROM Table1
Where [Auteurs 2] Is Not Null
UNION All
SELECT idlivre, [Auteurs 3], IIF([Auteurs 3] Is Not Null,3,Null) as rang
  FROM Table1
Where [Auteurs 3] Is Not Null
UNION ALL

............................

Mais en plus d'être long à taper, il s'est avéré qu'au bout d'un certain nombre d'Union (50 environ) la requête plante. Je peux bien sûr faire plusieurs requêtes et les unir après, mais je pense qu'il doit y avoir vraiment mieux que ce bidouillage pour résoudre le problème.
J'ai réessayé la méthode du UNPIVOT je n'y arrive toujours pas.

Si quelqu'un à une idée je suis preneur

Cordialement,

Terence hill

[Chtulus]

Bonjour,

La cosmétique se fait côté applicatif, non par une requête !

[Richard_35]

Bonjour Terence-hill et Chtulus,

En fait, si j'ai bien compris, ton problème se résume à trouver l'identifiant des auteurs à partir de ta première table.

Suggestion :

- dans Table1 ajouter n champs de manière à obtenir la structure suivante :
. Identifiant_livre
. auteur_1
. Id_auteur_1
. auteur_2
. Id_auteur_2
...
. auteur_n
. Id_auteur_n

- Ensuite une requête R1 sur Table3 :
. Identifiant auteur
. Nom_Prenom => nom & " " & prénom

- R11 : Lier R1 à Table1, via Nom_Prenom=auteur_1 et stocker Identifiant auteur dans Id_auteur_1 ;
- R11 : Lier R1 à Table1, via Nom_Prenom=auteur_2 et stocker Identifiant auteur dans Id_auteur_2 ;
...
- R1n : Lier R1 à Table1, via Nom_Prenom=auteur_n et stocker Identifiant auteur dans Id_auteur_n.

Tu devrais donc obtenir Table1 avec tous les Id générés automatiquement.
ATTENTION aux doublons de Nom_Prenom...

Je pense que tu voies l'esprit.

[terence-hill]

Merci pour vos réponses

Oui je vois le principe Richard_35, mais c'est pas tout à fait ce que je cherche, rajouter l'identifiant auteur dans la table 1 me permettra toujours pas de la lier à la table des auteurs facilement, j'aimerais obtenir le schéma suivant :

Table 3 (Auteurs) ----------->TABLE 5 <---------------Table 2 (Livres)

Avec ce schéma je pense être capable de faire toutes les requêtes nécessaires après. Mais si je met id_auteur dans ma table 1, il faudra que je relie la table 3 à la table 1 sur n champs, ca fait un peu beaucoup je trouve.

Peut-importe ce que j'essaye je retrouve toujours mon problème des mes n colonnes auteurs (Table 1) qui devraient faire 1 colonne de n * nb livres (Table 5).


Pour Chtulus :
"La cosmétique se fait côté applicatif, non par une requête " : cela ne veut pas dire grand chose pour moi, le cosmétique c'est la disposition des données je suppose et applicatif doit être Vba. Cela veut-il dire que pour mettre des n lignes en 1 colonnes il faut que je passe obligatoirement par de la programmation Vba?

Encore merci pour vos réponses.

Terence-hill

[Chtulus]

Bonjour tout le monde,

Envoyé par terence-hill

Pour Chtulus :
"La cosmétique se fait côté applicatif, non par une requête " : cela ne veut pas dire grand chose pour moi, le cosmétique c'est la disposition des données je suppose et applicatif doit être Vba.

C'est ça. Le SQL ne sert pas à faire de la présentation mais a extraire des données.

Envoyé par terence-hill

Cela veut-il dire que pour mettre des n lignes en 1 colonnes il faut que je passe obligatoirement par de la programmation Vba?

C'est préférable... Mais tout est possible !

[terence-hill]

Je vais donc m'essayer au VBa même si je ne connais pas ce langage, il faut que j'automatise ma création de table, ca pourra toujours servir.

Merci à vous pour votre aide

[Richard_35]

Bonjour à tous,

Ce n'est qu'une étape pour arriver à :

Envoyé par Terence-hill

j'aimerais donc créer une table reliant livre et auteur comme suit :
Table 4 :

Code :

Dès que Table1 :

Code :

1
2
3
4
Identifiant livre | auteur 1 | auteur 2 | ..... | auteur n
livre 1           | john     | jean     | ..... | elise
livre 2           | mick     | rien     | ..... | toujours rien
.........

sera devenu :

Code :

1
2
3
4
Identifiant livre | auteur 1 |Id_auteur1| auteur 2 |Id_auteur2 ..... | auteur n |Id_auteur n
livre 1           | john     | 125      | jean     | 208       ..... | elise    | 3       
livre 2           | mick     | 255      | rien     | null      ..... | tjrs rien| null
.........

tu peux arriver à ta créer ta Table4, non ?

[terence-hill]

Oui, au final en faisant :

Code :

1
2
3
4
select id livre, id auteur 1
union
select id livre, id auteur 2
jusqu'à n

C'est ce que j'avais fais au début mais au bout de 50 jointures ca ne passe plus sur Excel, du coup j'ai trois requêtes que je joins une nouvelle fois au final. Ça fait beaucoup de requête et une perte de temps énorme.

D'après mes recherches et Chtulus pour passer outre ce problème d'accumulation de boucles il faut soit faire un UNPIVOT, soit passer par le VBa .
Le UNPIVOT ca ne marche pas sur mon Access (2007) quand j'écris:

Code :

1
2
3
4
5
6
SELECT id_livre, auteur, rang
FROM (
SELECT id_livre, [auteurs 1], [auteurs 2], [auteurs 3].....,[Auteurs n]
from Table_1) 
UNPIVOT 
(rang for auteur In ([auteurs 1], [auteurs 2], [auteurs 3].....,[Auteurs n]))

Le UNPIVOT n'est pas reconnu quand je lance la requête, en plus (toujours d'après mes recherches) c'est une fonction à éviter.

Je passe donc au Vba pour tenter d'automatiser un peu ma création de table.
Je l'ai quand même créée en multipliant les jointures comme tu m'as dis Richard_35.
Il faudrait maintenant que j'essaye de le faire sous Vba pour les prochaines personnes qui importeront des données.
Si vous avez des idées du comment faire en VBa je suis preneur, un bon tutoriel sur comment sélectionner une case dans un tableau, comment recréer une table et y introduire des données... L'aide Access ne m'aide pas du tout en l'occurrence.

Merci encore

Terence hill

[Richard_35]

Excuses-moi, j'insite encore un peu...

Avec la table

Code :

1
2
3
4
Identifiant livre | auteur 1 |Id_auteur1| auteur 2 |Id_auteur2 ..... | auteur n |Id_auteur n
livre 1           | john     | 125      | jean     | 208       ..... | elise    | 3       
livre 2           | mick     | 255      | rien     | null      ..... | tjrs rien| null
.........

==> il faut plutôt créer une requête par auteur pour générer ta table lien. Pas besoin de requête UNION.

Ensuite, une seule macro peut déclencher toutes tes requêtes à la suite. Possibilité, également, de passer par du VBA (que je ne connais pas), pour "variabilser" le nom des requêtes et les lancer à l'intérieur d'une boucle.

[terence-hill]

Oui, je vois tout à fait ce que tu veux me dire mais c'est beaucoup trop long étant donné que j'ai énormément d'auteurs pour chaque livre. Je tente le VBA même si je n'y connais rien, j'associe VBa et SQL je m'y perd du coup, pour l'instant ca donne ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Dim SQL As String
 Dim SQLb As String
 Dim SQLc As string
 Dim i As Integer
 Dim n As Integer
 
 SQLb = "SELECT [id_livre], [auteurs "
 SQLc = "] FROM Table_1 Union all "
 SQL = "SELECT [id_livre], [auteurs 1] FROM Table_1 Union all " & SQLb
 
 For i = 2 To n
    SQL = SQL & i & SQLc & SQLb
 End
 
 SQL = "SELECT * INTO REL_ART_AUT FROM " & "(" & SQL & ");"
Me.lstResults.RowSource = SQL
 Me.lstResults.Requery

Après je n'ai pas encore réussir à définir n comme étant le numéro de la dernière colonne.
Ni à savoir comment lancer la macro au final.

J'y arriverais un jour Sinon j'ai pensé à de la vraie programmation avec deux boucles une pour les lignes et une pour les colonnes, et faire du pas à pas dans chaque case pour récupérer les données et les assigner dans une nouvelle table. Mais c'est trop dur pour moi je pense.

Merci encore

Terence-hill

[Chtulus]

C'est peut-être que j'ai rien compris dès le début

@terence-hill :

- Tu aurais la partie de ton MCD qui concerne tes tables "ECRIVAINS" ET "LIVRES" ?

Si on part du principe qu'un "écrivain" peut écrire plusieurs "livres" et qu'un "livre" peut être écrit par plusieurs "écrivains" il doit exister une table de jointure qui comme l'indique relie tout ça, nan ?

- Comment sont injectées les données dans les tables ?

[terence-hill]

Je vais repartir du début, j'ai hérité d'un jeu de données qui comme la plupart du temps est super mal agencé. Il n'y avait rien de joint, c'était des fichiers Excel avec deux variables sur une même colonne, des doublons etc...
J'ai réussi à en ressortir la TABLE 1 et la TABLE 3 :

TABLE 1 :
ID LIVRE | AUTEUR 1 |AUTEUR 2 |....|AUTEUR N| et autres caractéristiques des livres

TABLE 3 :
ID AUTEUR | caractéristiques des auteurs

J'ai donc plusieurs auteurs pour un livre donné, 60 environ le max que j'ai vu.
J'ai créé une TABLE 2 concernant les caractéristiques des livres

TABLE 2
ID LIVRE | caractéristiques des livres

Mes tables 2 et 3 sont en 3FN donc c'est bon pour celles là, il faut juste que je créer la table entre les deux qui est :

TABLE 5 :
ID LIVRE | ID auteur | rang de l'auteur |

j'ai les clés de chaque table dans cette dernière et le rang correspond à la valeur de la colonne n dans la TABLE 1.

Mon MCD serait donc :
TABLE 2 : ID LIVRE | caractéristiques des livres
(relie par ID LIVRE)
TABLE 5 : #ID LIVRE | #ID auteur | rang de l'auteur |
(relie par ID auteur)
TABLE 3 : ID AUTEUR | caractéristiques des auteurs

C'est pour atteindre ce schéma que je voulais mettre mes n colonnes auteurs de ma TABLE 1 en 1 colonnes dans une nouvelle table.
La table de jointure qui est censée exister c'est celle là que j'essaye de créer Chtulus
SQL ne fonctionnant pas je suis parti sur du VBa que je connais pas.

Merci encore

Terence-hill

[terence-hill]

J'ai pas réussi en Vba, je ne suis pas encore très doué avec ce langage, j'ai donc gardé mes multiples jointures pour mettre mes colonnes en lignes.
Il semblerait qu'en SQL ce soit le seul moyen.

Merci à vous pour votre aide.

Terence-hill

[Simplifi]

Hello
pour transformer ta table 1, tu peux utiliser une requête ajout dans une nouvelle table:
ajouter ID LIVRE | AUTEUR 1 | "1"
puis ensuite la même avec auteur 2 et "2"
rien ne t'empêche de faire fonctionner plus de 50 requêtes l'une après l'autre

tu peux aussi récupérer le SQL de la première, il est très simple, et le bricoler dans une boucle VBA

[terence-hill]

Merci Simplifi,

C'est une très bonne idée, je vais refaire mes imports en utilisant cette méthode je pense, le VBA doit être assez facile en plus.

merci beaucoup

p.s. je peux mettre qu'une seule fois le +1 mais ca vaut bien un +30 ça

Terence-hill