Discussion :

[champomy62]

Bonjour,

Je vous explique mon problème :

J'ai une table nommée '"O_STD_TablesDiverses" qui contient 55 colonnes. Les 5 premières sont définis par un nom signifiant(STD_NDEM, STD_CSOC, STD_CTDO, STD_CDOM, STD_NOMEDITION) et les 50 autres colonnes portent comme nom "STD_CZONE01" et "STD_VAL01" et ainsi de suite.

Les champs "STD_CZONE" sont considérées comme des colonnes car on ne sait jamais à l'avance ce que les zones contiendront comme valeur.

J'aimerai récupéré pour un enregistrement, les valeurs contenus dans les colonnes qui commencent par "STD_CZONE".

Exemple :

CZONE01 | STD_LDOC
CZONE02 | STD_CRITERE

J'ai entendu parler de pivot, est-cela ?

J'ai un début de requête :

use [mabase]
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS, DIVERS
WHERE TABLE_NAME='DIVERS' AND COLUMN_NAME like 'STD_CZONE%' AND STD_NDIV = '120000002'

[SQLpro]

Ce qui est stupide c'est que votre table viole la première forme normale ou tout au moins son esprit.
En effet, la première forme normale spécifie que chaque attribut (donc colonne) d'une relation (donc table) doit être ATOMIQUE (donc ne contenir qu'une seule valeur !).
Convenez que des colonnes de mêmes noms avec juste un numéro différent pourraient être résumées dans un tableau de valeurs, au sein d'une seule et même colonne, car vous ne différenciez pas la colonne Col1 de la colonne Col8 sauf par son n° d'ordre, qui n'a, a l'évidence aucune sémantique (il ne signifie rien....).
Donc vous avez violé la première forme normale....

Une bonne modélisation aurait conduit à la solution suivante :
O_STD_TablesDiverses sans ces colonnes "violantes"
Une table O_STD_TablesDiverses_Detail avec comme colonne :
- clef de O_STD_TablesDiverses
- numéro
- valeur
Avec le couple "clef de O_STD_TablesDiverses" + "numéro" comme PK.

Dès lors votre question n'a pas de raison d'être.

Moralité : quand on fait de la merde en entrée on a de la merde en sortie, ce que les ricains résument en "garbage in, garbage out..."

L'opérateur PIVOT n'est pas fait pour ce cas de figure car il est utilisé essentiellement pour agréger des données. En sus c'est un opérateur cosmétique !

Seule solution pour le long terme : remodéliser votre base...

A +

[champomy62]

Merci de votre réponse.

Le soucis est qu'on ne connait jamais à l'avance le nom des valeurs contenu dans les colonnes STD_CZONE.

Le fichier transmis contient un exemple de ce qui est contenu dans la table

Convertissez le format en CSV pour l'ouvrir dans votre tableur.

Je suis d'accord sur le fait que c'est mal modelisé mais la décision n'est pas de mon ressort.

[champomy62]

L'avantage de cette technique est que cela nous permet d'être souple vis à vis des spécificités clients.

Cette table DIVERS est relié à une table CRITERE qui nous permet de vérifier que le critère existe et si c'est le cas, on insert les données sinon c'est rejeté.

[JLCantara]

Bonjour Champomy62,

L'ayatollah SQLPro a émis une fatwa: 'Allah ou akhbar '. J'ai l'insulte discrète mais la curiosité énorme.

Ton problème est extrêmement intéressant puisque c'est un cas de documentation.

Bref, il suffit de créer la requête de façon dynamique dans une 'Procédure stockée'. Voici le texte:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		JLCantara
-- Create date:   2012.06.21
-- Description:	Dynamic query.
-- =============================================
CREATE PROCEDURE [dbo].[FetchZone]
   @P CHAR(2) = '01'
AS
 
BEGIN
 
	SET NOCOUNT ON;
 
	DECLARE @SQLQuery AS VARCHAR(500)
 
	/* Build Transact-SQL String with parameter value */
	SET @SQLQuery = 'SELECT STD_CZONE' + @P + ', STD_VAL' + @P + ' FROM Test04'
 
	/* Execute Transact-SQL String */
	EXECUTE(@SQLQuery)
 
END

Comme tu vois, y a pas de quoi sabré le champagne. Pourtant, la situation se présente de temps en temps pour des requêtes simili construites par l'utilisateur - j'ai récemment eu le cas pour un client 'sophistiqué'. En fait, des requêtes d'une redoutable complexité peuvent être construites: beaucoup de requêtes 'système' de SQL sont ainsi faites.

P.S. - Ajouter un test pour assurer que @P a 2 caractères.
Test04 est bien sur ta table de données.

Bonnes requêtes,
JLCantara.

[elsuket]

L'ayatollah SQLPro a émis une fatwa: 'Allah ou akhbar '. J'ai l'insulte discrète mais la curiosité énorme.

N'empêche, quand le modèle de données est correct, l'expression des requêtes n'en est que simplifiée

J'aurai plutôt recommandé l'utilisation de sp_executesql, surtout s'il y a filtres à paramétrer.

@++

[champomy62]

Bonjour,

Merci de vos réponses.

J'avais trouvé comment faire mais je vais améliorer ma façon de faire grâce à vos exemples.

Actuellement ce que j'ai :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
use [MABASE]
 
declare @counter int
declare @str nvarchar(50)
declare @query nvarchar(400)
	set @counter = 0
	set @str = ''
	while @counter < 10
	begin
	  set @counter = @counter + 1
	  set @str = 'STD_CZONE0' + CAST(@counter as NVARCHAR)
	  set @query = 'SELECT ' + @str + ' FROM INFORMATION_SCHEMA.COLUMNS, DIVERS WHERE TABLE_NAME=''DIVERS'' AND COLUMN_NAME like  ''' + @str + ''' AND STD_NDIV = ''120000002'''
	 exec sp_executesql @query
	end

[champomy62]

Est-il possible d'avoir une requête SELECT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT COLUMN_NAME LIKE 'STD_CZONE%'

Sinon avec ma requête, le plan d'exécution est lourd...

[elsuket]

Bonjour,

Voici :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE PROCEDURE usp_maProcedure
	@_STD_NDIV int
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @sql nvarchar(max)
		, @cr nchar(2) = NCHAR(13) + NCHAR(10)
		, @tab nchar(1) = NCHAR(9)
 
	SELECT	@sql = CASE
				WHEN @sql IS NULL THEN 'SELECT	' + COLUMN_NAME
				ELSE @sql + @cr + @tab + ', ' + COLUMN_NAME
			END
	FROM		INFORMATION_SCHEMA.COLUMNS
	WHERE		TABLE_CATALOG = DB_NAME()
	AND		TABLE_SCHEMA = 'dbo'
	AND		TABLE_NAME = 'DIVERS'
	AND		COLUMN_NAME LIKE 'STD_CZONE%'
	ORDER BY	ORDINAL_POSITION
 
	SET @sql = @sql + @cr + 'FROM	dbo.DIVERS'
		+ @cr + 'WHERE	STD_NDIV = @STD_NDIV'
 
	-- PRINT @sql
 
	EXEC sp_executesql
		@sql
		, N'@STD_NDIV int'
		, @STD_NDIV = @_STD_NDIV
END

Ou encore avec les vues de métadonnées de SQL Server :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
CREATE PROCEDURE usp_maProcedure
	@_STD_NDIV int
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @sql nvarchar(max)
		, @cr nchar(2) = NCHAR(13) + NCHAR(10)
		, @tab nchar(1) = NCHAR(9)
 
	SELECT	@sql = CASE
				WHEN @sql IS NULL THEN 'SELECT	' + C.name
				ELSE @sql + @cr + @tab + ', ' + C.name
			END
	FROM		sys.schemas AS S
	INNER JOIN	sys.tables AS T
				ON T.schema_id = S.schema_id
	INNER JOIN	sys.columns AS C
				ON T.object_id = C.object_id
	WHERE		S.name = 'dbo'
	AND		T.name = 'DIVERS'
	AND		C.name LIKE 'STD_CZONE%'
	ORDER BY	C.column_id
 
	SET @sql = @sql + @cr + 'FROM	dbo.DIVERS'
		+ @cr + 'WHERE	STD_NDIV = @STD_NDIV'
 
	-- PRINT @sql
 
	EXEC sp_executesql
		@sql
		, N'@STD_NDIV int'
		, @STD_NDIV = @_STD_NDIV
END

Vous appelez donc la procédure stockée dbo.usp_maProcedure en passant seulement une valeur pour STD_NDIV, et la procédure fait le reste

@++

[Rei Ichido]

Le mieux serait évidemment de revoir le modèle !

Cela dit, connaissant bien le principe des Progiciels avec des modèles pourris, je sais qu'on est souvent dans la mouise !

Aimant bien les vues, et vu que SQL Server se débrouille pour démêler d'abord la partie "en dur" avant d'accéder aux tables, pour des utilisations sommaires je ferais une vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE VIEW V_STD_CZONE AS
SELECT 'CZONE01' AS ColName
          ,CZONE01  AS CZONE
          ,STD_NDEM, STD_CSOC, STD_CTDO, STD_CDOM, STD_NOMEDITION
    FROM DIVERS
UNION ALL
SELECT 'CZONE02' AS ColName
          ,CZONE02  AS CZONE
          ,STD_NDEM, STD_CSOC, STD_CTDO, STD_CDOM, STD_NOMEDITION
    FROM DIVERS
[etc.]

De préférence à exécuter avec une procédure.
Et ensuite interroger la vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
SELECT *
   FROM V_STD_CZONE 
  WHERE ColName = 'CZONE01'
     AND ...

[champomy62]

Merci. Grâce à vos réponses j'ai pu résoudre mon problème plus simplement grâce au SQL

Revoir le modèle, oui le problème est que nous connaissons jamais le nom des zones....

Dans un progiciel, comment faîtes-vous lorsque le nom des zones peuvent varier entre plusieurs clients ?

[Rei Ichido]

Eh bien on a de belles tables d'identifications des valeurs.

C'est infiniment plus propre et plus modulable !