Requete avec champs identiques & cumuler champs différents

[guigui69]

Bonsoir à tous,

Désolé pour le titre pas très explicite mais je ne sais pas comment expliquer clairement ma demande.

Pour faire simple, voici le résultat de ma requête:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
voiture	     couleur1	porte 	 OPTION
voiture1	rouge	5	clim
voiture1	rouge	5	lecteur cd
voiture1	rouge	5	toit ouvrant
voiture1	rouge	5	gante
voiture1	rouge	5	siege cuir
voiture2	vert	3	clim
voiture2	vert	3	lecteur cd
voiture2	vert	3	toit ouvrant
voiture2	vert	3	gante
voiture2	vert	3	siege cuir
voiture2	vert	3	clim

Ma requete est du type

select voiture, couleur, option FROM car INNER...... WHERE societe='001'

Je voudrait savoir si en modifiant ma requete SQL je pourrait arriver à ressortir ceci:

Code :

1
2
3
4
 
voiture	    couleur1	porte 	         OPTION
voiture1    rouge        5          clim siege cuir gante autoradio
voiture2    vert         3           toit ouvrant lecteur cd

Est-ce possible de réaliser ceci dans une requête?

Merci d'avance pour votre aide

guigui69

[elsuket]

Bonjour,

Normalement ceci devrait être réalisé du côté applicatif, puisqu'il s'agit de présenter des données, en plus de les retrouver.
Or la première forme normale (c'est-à-dire la base de toute bonne conception de bases de données) indique que toute valeur doit être atomique.
Ce n'est pas le cas de ce que vous cherchez

Comme vous souhaitez faire cela du côté bases de données, et qu'en plus votre conception laisse à désirer, la requête est assez tarabiscotée :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
;WITH
	CTE AS
	(
		SELECT	DISTINCT voiture
			, couleur
			, porte
		FROM	@car
	)
SELECT		A.voiture
		, A.couleur
		, A.porte
		, LEFT(L.liste_option, LEN(L.liste_option) - 1) AS liste_option
FROM		CTE AS A
CROSS APPLY (
			SELECT		DISTINCT [options] + ', '
			FROM		car AS T
			INNER JOIN	CTE AS C
						ON T.voiture = C.voiture
						AND A.voiture = T.voiture
			FOR XML PATH ('')
		) AS L(liste_option)

Vous remarquerez que vous avez mis deux fois l'option clim pour la voiture deux.
Vu la consommation de carburant qu'engendre une seule climatisation sur une voiture, je doute que la plupart des voitures que votre client ou votre entreprise loue en disposent de deux ...
Ce type de "conception" vous a de plus forcé à répéter autant de fois que d'options le nom, la couleur et les nombre de portes de chaque voiture ...
Donc en plus de perdre de l'espace, votre BD n'est pas robuste.

Vous pourriez par exemple avoir :

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
CREATE TABLE voiture
(
	voiture_id smallint IDENTITY NOT NULL CONSTRAINT PKvoiture PRIMARY KEY
	, nom varchar(32) NOT NULL
	, immatriculation varchar(16) NOT NULL CONSTRAINT UQvoiture UNIQUE
)
GO
 
CREATE TABLE voiture_couleur
(
	voiture_couleur_id tinyint IDENTITY NOT NULL CONSTRAINT PKvoiture_couleur PRIMARY KEY
	, nom varchar(32) NOT NULL CONSTRAINT UQvoiture_couleur UNIQUE
)
GO
 
CREATE TABLE voiture_porte
(
	voiture_porte_id tinyint IDENTITY NOT NULL CONSTRAINT PKvoiture_porte PRIMARY KEY
	, nombre tinyint NOT NULL CONSTRAINT CHKvoiture_porte__nombre CHECK (nombre BETWEEN 2 AND 5)
)
GO
 
CREATE TABLE voiture_option
(
	voiture_option_id tinyint IDENTITY NOT NULL CONSTRAINT PKvoiture_option PRIMARY KEY
	, nom varchar(32) NOT NULL CONSTRAINT UQvoiture_option__nom UNIQUE
)
GO
 
CREATE TABLE voiture_caracteristique
(
	voiture_caracteristique_id int IDENTITY NOT NULL CONSTRAINT PKvoiture_caracteristique PRIMARY KEY
	, voiture_id smallint NOT NULL CONSTRAINT FKvoiture_caracteristique__voiture_id FOREIGN KEY (voiture_id) REFERENCES voiture
	, voiture_couleur_id tinyint NOT NULL CONSTRAINT FKvoiture_caracteristique__voiture_couleur_id FOREIGN KEY (voiture_couleur_id) REFERENCES voiture_couleur
	, voiture_porte_id tinyint NOT NULL CONSTRAINT FKvoiture_caracteristique__voiture_porte FOREIGN KEY (voiture_porte_id) REFERENCES voiture_porte
	, voiture_option_id tinyint NOT NULL CONSTRAINT FKvoiture_caracteristique__voiture_option_id FOREIGN KEY (voiture_option_id) REFERENCES voiture_option
	, commentaire varchar(128)
	, CONSTRAINT UQvoiture_caracteristique__voiture_id__voiture_couleur_id__voiture_porte_id__voiture_option_id UNIQUE (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id)
)
GO
 
INSERT	voiture (nom, immatriculation) VALUES ('voiture1', 'VOITURE1')
INSERT	voiture (nom, immatriculation) VALUES ('voiture2', 'VOITURE2')
GO
 
INSERT voiture_couleur (nom) VALUES ('rouge')
INSERT voiture_couleur (nom) VALUES ('vert')
GO
 
INSERT	voiture_porte (nombre) VALUES (3)
INSERT	voiture_porte (nombre) VALUES (5)
GO
 
INSERT	voiture_option (nom) VALUES ('clim')
INSERT	voiture_option (nom) VALUES ('lecteur cd')
INSERT	voiture_option (nom) VALUES ('toit ouvrant')
INSERT	voiture_option (nom) VALUES ('gante')
INSERT	voiture_option (nom) VALUES ('siege cuir')
GO
 
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 1, 2, 1)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 1, 2, 2)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 1, 2, 3)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 1, 2, 4)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 1, 2, 5)
GO
 
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 2, 1, 1)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 2, 1, 2)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 2, 1, 3)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 2, 1, 4)
INSERT	voiture_caracteristique (voiture_id, voiture_couleur_id, voiture_porte_id, voiture_option_id) VALUES (1, 2, 1, 5)
GO
 
CREATE VIEW voiture_caracteristique_view
AS
	SELECT		V.voiture_id
			, V.nom AS voiture_nom
			, VCO.nom AS couleur_nom
			, VP.nombre AS portes_nombre
			, VO.nom AS option_nom
	FROM		dbo.voiture_caracteristique AS VCA
	INNER JOIN	dbo.voiture AS V
				ON VCA.voiture_id = V.voiture_id
	INNER JOIN	dbo.voiture_couleur AS VCO
				ON VCA.voiture_couleur_id = VCO.voiture_couleur_id
	INNER JOIN	dbo.voiture_porte AS VP
				ON VCA.voiture_porte_id = VP.voiture_porte_id
	INNER JOIN	dbo.voiture_option AS VO
				ON VCA.voiture_option_id = VO.voiture_option_id
GO

Ce que vous avez fait jusque là est dans la vue

@++

[guigui69]

Merci pour votre réponse je regarde ca,

j'ai oublié de précisé que nous somme sous ms sql 2000 sp4.

Je vais regarder ca du coté de notre outil alors.

Je pensait qu'il y avait un moyen simple à travers une requête d'avoir ceci.

guigui69

[elsuket]

La requête que je vous ai donné ne fonctionnera pas sous SQL Server 2000 ...

Celle-ci oui :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT	voiture
	, couleur1
	, porte
	, LEFT(liste_options, LEN(liste_options) - 1) AS liste_options
FROM	(
		SELECT	DISTINCT voiture
			, couleur1
			, porte
			, (
				SELECT	options + ', '
				FROM	dbo.car AS T2
				WHERE	T2.voiture = T1.voiture
				FOR XML PATH ('')
			) AS liste_options
		FROM	dbo.car AS T1
	) AS SUB

Je ne sais pas pourquoi je ne l'ai pas trouvée ce matin, 'devais pas encore être bien réveillé

@++

[guigui69]

Merci pour votre aide. Désolé pour la réponse tardive.

voici ma requête

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
 
 
SELECT 
 LTRIM(RTRIM(tgEmpresa.CPostal)+'*'+ tgMarca.Descrip) AS Site,           tcVeh.NumPedidoFab AS "No_VN",           
 tcVeh.Chasis,   '' AS code_marque,		    
  tgMarca.Descrip AS Marque,   
 ''  	AS CodeVersion,	          
  tgVersion.Descrip  	AS Version,	        
    tcVeh.Color AS "Code_Teinte",  
tccolor.descrip AS "Teinte",  
'' AS tarif,  
'' AS "date_tarif", 
 '' AS "Codes_Options", 
 '' AS "Libelles_Options",  
DATEDIFF(day, tcVeh.FecEntrada, getdate()) AS Anciennete,		           
 tcVeh.Tapiz AS CodeSellerie,   
 tctapiz.descrip AS SellerieInterieure,  
 
tgcategoria.descrip AS genre, 
 tcvehOpcion.Opcion,  
tcvehOpcion.Descrip      
 
FROM   			tcVeh    	      
LEFT OUTER JOIN tgEmpresa  ON  	         tcVeh.Emp = tgEmpresa.Emp    			
LEFT OUTER JOIN TgMarca ON     	         tcveh.Marca = tgMarca.Marca             LEFT OUTER JOIN tgModelo ON        	      tcveh.Marca = tgModelo.Marca AND    	         tcveh.Modelo = tgModelo.Modelo         
 LEFT OUTER JOIN tgVersion  ON           	   tcveh.Marca   = tgVersion.Marca AND       	      tcveh.Modelo  = tgVersion.Modelo AND    	         tcveh.Version = tgVersion.Version    		
LEFT OUTER JOIN tgPtoVenta  ON     	      tcVeh.Emp 	= tgPtoVenta.Emp AND    	         tcVeh.PuntoVenta = tgPtoVenta.PuntoVenta     			
LEFT OUTER JOIN tccolor  ON  				tcveh.marca = tccolor.marca AND   				tcveh.color = tccolor.color     		
LEFT OUTER JOIN tctapiz  ON  				tcveh.marca = tctapiz.marca AND   				tcveh.tapiz = tctapiz.tapiz             
 LEFT OUTER JOIN tgVendedor ON  	         tcveh.Emp = tgVendedor.Emp AND       	      tcveh.reservaVendedor = tgVendedor.Vendedor     			
LEFT OUTER JOIN tgcliente ON  	         tcveh.reservaCliente = tgCliente.Codigo    			
LEFT OUTER JOIN tcvehopcion ON  				tcveh.emp = tcvehopcion.emp AND   				tcveh.numinterno = tcvehopcion.numinterno     			
LEFT OUTER JOIN tcvehsubstatus ON  				tcveh.STATUS=tcvehsubstatus.STATUS AND   				tcveh.substatus=tcvehsubstatus.substatus    			
LEFT OUTER JOIN tcubicacion ON  				tcveh.emp 		 = tcubicacion.emp AND  				tcveh.ubicacion = tcubicacion.ubicacion    			
LEFT OUTER JOIN tgcategoria ON  				tcveh.categoria = tgcategoria.categoria    			
LEFT OUTER JOIN ttCortesiaCategoria ON   				ttCortesiaCategoria.CortesiaCategoria = tcVeh.CortesiaCategoria                  WHERE  		
tcveh.reservafirme = '0' AND         
  tcveh.EsVO = 0 AND   			
tcveh.STATUS < 30 AND  			
tcveh.STATUS > 0 AND  			
tcveh.substatus NOT IN (11,12,21,22,23) AND  
 (DATEDIFF(day, tcVeh.FecEntrada, getdate())) IS NOT NULL AND  
esdemo ='0'

Les deux options que je voudrait mettre sur la meme ligne c'est ces 2 options: tcvehOpcion.Opcion, tcvehOpcion.Descrip

Je suis sur cette requete depuis 3 heures et je bloque sur le FOR XML PATH.

Pourriez-vous aiguiller ? Que mettre entre '' dans FOR XML PATH?

Merci d'avance pour votre aide.

guigui69

[guigui69]

Je crois que FOR XML PATH ('') n'existe pas sous ms sql server 2000

[elsuket]

Bonjour,

Je crois que FOR XML PATH ('') n'existe pas sous ms sql server 2000

C'est effectivement le cas.
Donc comme je vous l'ai dit, vous devez effectuer cela côté applicatif.

En outre votre requête est assez brouillon en présentation, ce qui ne facilite pas sa lecture.
Vous n'avez pas non plus qualifié les tables par le schéma auquel elles appartiennent, ce que oblige SQL Server à le chercher à votre place.

Enfin :

Code :

(DATEDIFF(day, tcVeh.FecEntrada, getdate())) IS NOT NULL

Ne retourne NULL que si tcVeh.FecEntrada est NULL.

Donc on peut directement écrire :

Code :

V.FecEntrada IS NOT NULL

Ce qui donne finalement :

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
SELECT		LTRIM(RTRIM(E.CPostal) + '*' + M.Descrip) AS Site
		, V.NumPedidoFab AS "No_VN"
		, V.Chasis
		, '' AS code_marque
		, M.Descrip AS Marque
		, ''  	AS CodeVersion
		, VE.Descrip AS Version
		, V.Color AS "Code_Teinte"
		, C.descrip AS "Teinte"
		, '' AS tarif
		, '' AS "date_tarif"
		, '' AS "Codes_Options"
		, '' AS "Libelles_Options"
		, DATEDIFF(day, V.FecEntrada, GETDATE()) AS Anciennete
		, V.Tapiz AS CodeSellerie
		, T.descrip AS SellerieInterieure
		, CAT.descrip AS genre
		, O.Opcion
		, O.Descrip
FROM		dbo.tcVeh AS V
LEFT JOIN	dbo.tgEmpresa AS E
			ON V.Emp = E.Emp    			
LEFT JOIN	dbo.TgMarca AS M
			ON V.Marca = M.Marca             
LEFT JOIN	dbo.tgModelo MD
			ON V.Marca = MD.Marca
			AND  V.Modelo = MD.Modelo         
LEFT JOIN	dbo.tgVersion AS VE
			ON V.Marca = VE.Marca 
			AND V.Modelo = VE.Modelo 
			AND V.Version = VE.Version    		
LEFT JOIN	dbo.tgPtoVenta AS PV
			ON V.Emp = PV.Emp
			AND V.PuntoVenta = PV.PuntoVenta     			
LEFT JOIN	dbo.tccolor AS C
			ON V.marca = C.marca
			AND V.color = C.color     		
LEFT JOIN	dbo.tctapiz AS T
			ON V.marca = T.marca 
			AND V.tapiz = T.tapiz
LEFT JOIN	dbo.tgVendedor AS VD
			ON V.Emp = VD.Emp
			AND V.reservaVendedor = VD.Vendedor     			
LEFT JOIN	dbo.tgcliente AS CL
			ON V.reservaCliente = CL.Codigo    			
LEFT JOIN	dbo.tcvehopcion AS O
			ON V.emp = O.emp
			AND V.numinterno = O.numinterno     			
LEFT JOIN	dbo.tcvehsubstatus AS SS
			ON V.STATUS = SS.STATUS
			AND V.substatus = SS.substatus    			
LEFT JOIN	dbo.tcubicacion AS CUB
			ON V.emp = CUB.emp
			AND V.ubicacion = CUB.ubicacion    			
LEFT JOIN	dbo.tgcategoria AS CAT
			ON V.categoria = CAT.categoria    			
LEFT JOIN	dbo.ttCortesiaCategoria AS CC
			ON CC.CortesiaCategoria = V.CortesiaCategoria                  
WHERE  		V.reservafirme = '0'
AND		V.EsVO = 0 
AND		V.STATUS < 30 
AND		V.STATUS > 0 
AND		V.substatus NOT IN (11,12,21,22,23) 
AND		V.FecEntrada IS NOT NULL
AND		esdemo ='0'

Un peu plus aéré

@++

[guigui69]

Merci pour votre réponse

Vous n'avez pas non plus qualifié les tables par le schéma auquel elles appartiennent, ce que oblige SQL Server à le chercher à votre place.

je ne comprend pas

[elsuket]

Voyez ici

@++