PIVOT 2 lignes en 2 colonne suivant valeur d'un autre champ

**Firlfire** · 21/08/2018, 17h30

Bonjour!

J'ai ces 2 tables (DOCU_PROD.K_DOCUMENT=DOCUMENTS.K_DOCUMENT) :
Nom : Marge.PNG
Affichages : 1276
Taille : 24,5 Ko

Je souhaite faire une Query pour faire pivoter DOCU_PROD.TOTAL pour un (K_PRODUCT et F_LOT) donné. Au maximum il pourra y avoir que 2 colonnes de sortie car elle dépende de DOCUMENTS.REFERENCE

J'ai essayer mais je ne parviens pas a utiliser le pivot de base.. (Msg 8114 : Erreur de conversion du type de données nvarchar en float.) Pourtant F_LOT et TOTAL sont des float

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
select 'K_PRODUCT' AS K_PRODUCT, [F_PREV], [F_PLAN]
from (
	select Docu.REFERENCE, DP.K_PRODUCT, DP.TOTAL from DOCU_PROD DP
		join DOCUMENTS Docu on DP.K_DOCUMENT=Docu.K_DOCUMENT
		where DP.K_PRODUCT=54 and DP.F_LOT=62
 ) as SourceTable
PIVOT
(
 Max(TOTAL)
 FOR K_PRODUCT IN ([F_PREV], [F_PLAN])
) as PivotTable;

Pour "Réultats 1" ci-dessus:
- La query récupère les 2 lignes où K_PRODUCT=1 ET F_LOT=1,
- Le pivot met DOCU_PROD.TOTAL (600) dans V_PREV car DOCUMENTS.REFERENCE commence par D (like 'D%')
- Le pivot met DOCU_PROD.TOTAL (800) dans F_PLAN car DOCUMENTS.REFERENCE commence par IPC (like 'IPC%')

Invité · 21/08/2018, 17h59

Problème de conversion de données, inconsistance entre les types...
Peux-tu nous sortir les DDL de tes tables ?

**Firlfire** · 22/08/2018, 09h25

Bonjour,
Je mets les script de création des table en bas.

Je suis parvenu à la faire "fonctionner", dans le sens ou il n'y a pas d'erreur de SQL Serveur:

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
select 'K_PRODUCT', [250] as F_PREV, [251] as [F_PLAN]
from (
	select DP.K_PRODUCT, DP.TOTAL from DOCU_PROD DP
		join DOCUMENTS Docu on DP.K_DOCUMENT=Docu.K_DOCUMENT
		where DP.K_PRODUCT=54 and DP.F_LOT=62
 ) as SourceTable
PIVOT
(
 Max(TOTAL)
 FOR K_PRODUCT IN ([250], [251])
) as PivotTable;

Maintenant le problème c'est que je n'arrive pas a faire pivoter TOTAL :
Nom : result.PNG
Affichages : 1058
Taille : 3,9 Ko

2ème problème : comment je peux faire pivoter le 600 dans F_PREV car Docu.REFERENCE like 'D%' et le 750 dans F_PLAN' car Docu.REFERENCE like 'IPC%'

Voilà les script de créations (/!\ je ne peux pas supprimer/modifier les champ, sauf ceux dont le nom commance par 'F_')

DOCU_PROD

Code SQL :

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
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
/****** Object:  Table [DOCU_PROD]    Script Date: 22/08/2018 09:18:22 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [DOCU_PROD](
	[K_DOCUMENT] [float] NOT NULL,
	[K_PRODUCT] [float] NOT NULL,
	[K_RELATION] [int] NOT NULL,
	[D_CHANGE] [datetime] NOT NULL,
	[MAIN] [varchar](1) NOT NULL,
	[COMMENT] [nvarchar](128) NULL,
	[DISCOUNT] [float] NULL,
	[PRICE] [float] NULL,
	[QUANTITY] [float] NULL,
	[VAT] [float] NULL,
	[TOTAL] [float] NULL,
	[K_SORT] [int] NULL,
	[RATE] [float] NULL,
	[CONSO_TOTAL] [float] NULL,
	[D_RATE] [datetime] NOT NULL,
	[CONSO_CURRCY] [int] NULL,
	[CONSO_RATE] [float] NULL,
	[D_REPLIC] [datetime] NOT NULL,
	[K_REPLICUSER] [int] NOT NULL,
	[F_LOT] [float] NULL,
	[F_FAMILY] [int] NULL,
 CONSTRAINT [PKDOCU_PROD] PRIMARY KEY CLUSTERED 
(
	[K_DOCUMENT] ASC,
	[K_PRODUCT] ASC,
	[K_RELATION] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT ((1)) FOR [K_RELATION]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT (getdate()) FOR [D_CHANGE]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT ('0') FOR [MAIN]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT ((1)) FOR [QUANTITY]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT (getdate()) FOR [D_RATE]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT (getdate()) FOR [D_REPLIC]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT ((0)) FOR [K_REPLICUSER]
GO
 
ALTER TABLE [DOCU_PROD] ADD  DEFAULT ((0)) FOR [F_PLAN]
GO
 
ALTER TABLE [DOCU_PROD]  WITH CHECK ADD  CONSTRAINT [FK_DOCUMENTS_DOCU_PROD1] FOREIGN KEY([K_DOCUMENT])
REFERENCES [DOCUMENTS] ([K_DOCUMENT])
ON DELETE CASCADE
GO
 
ALTER TABLE [DOCU_PROD] CHECK CONSTRAINT [FK_DOCUMENTS_DOCU_PROD1]
GO
 
ALTER TABLE [DOCU_PROD]  WITH CHECK ADD  CONSTRAINT [FK_PRODUCTS_DOCU_PROD2] FOREIGN KEY([K_PRODUCT])
REFERENCES [PRODUCTS] ([K_PRODUCT])
ON DELETE CASCADE
GO
 
ALTER TABLE [DOCU_PROD] CHECK CONSTRAINT [FK_PRODUCTS_DOCU_PROD2]
GO

DOCUMENTS :

Code SQL :

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
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
/****** Object:  Table [DOCUMENTS]    Script Date: 22/08/2018 09:19:09 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [DOCUMENTS](
	[K_DOCUMENT] [float] NOT NULL,
	[NAME] [nvarchar](80) NOT NULL,
	[D_CREATE] [datetime] NOT NULL,
	[D_CHANGE] [datetime] NOT NULL,
	[K_USER] [int] NOT NULL,
	[CHANGEDBY] [nvarchar](16) NULL,
	[OPENED] [varchar](1) NOT NULL,
	[REFERENCE] [nvarchar](64) NULL,
	[REFFIXED] [varchar](1) NOT NULL,
	[EMAIL] [varchar](1) NOT NULL,
	[ATTACHMENTS] [int] NULL,
	[MEMO] [ntext] NULL,
	[MEMOFORMATTED] [varchar](1) NOT NULL,
	[D_USED] [datetime] NULL,
	[SECURITYCHANGES] [nvarchar](400) NULL,
	[KEEPSECURITY] [varchar](1) NOT NULL,
	[P_D_USED] [datetime] NULL,
	[UPLOADSTATE] [varchar](1) NULL,
	[D_REPLIC] [datetime] NOT NULL,
	[K_REPLICUSER] [int] NOT NULL,
	[F_REFINSA] [nvarchar](32) NULL,
	[F_CESURE1] [float] NULL,
	[F_CESURE2] [float] NULL,
	[F_CESURE3] [float] NULL,
	[F_CESURE4] [float] NULL,
	[F_PLANNEDMARGE] [float] NULL,
	[F_MONTANTST] [float] NULL,
	[F_STATUS] [int] NULL,
 CONSTRAINT [PKDOCUMENTS] PRIMARY KEY CLUSTERED 
(
	[K_DOCUMENT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT (getdate()) FOR [D_CREATE]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT (getdate()) FOR [D_CHANGE]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ('?') FOR [CHANGEDBY]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ('1') FOR [OPENED]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ('0') FOR [REFFIXED]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ('0') FOR [EMAIL]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ('0') FOR [MEMOFORMATTED]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT (getdate()) FOR [D_USED]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ('0') FOR [KEEPSECURITY]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT (getdate()) FOR [D_REPLIC]
GO
 
ALTER TABLE [DOCUMENTS] ADD  DEFAULT ((0)) FOR [K_REPLICUSER]
GO

**Firlfire** · 22/08/2018, 10h27

Bonjour,

J'ai mis au point cette requête mais je pense qu'il y a mieux/plus opti/etc.
Style avec un PIVOT ou autre ?

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
select src.K_PRODUCT, Sum(src.TMP_PREV) as V_PREV, Sum(src.TMP_PLAN) as V_PLAN
	from (
		select DP.K_PRODUCT
			  ,case when Docu.REFERENCE like 'D%' then TOTAL else  0 end as TMP_PREV
			  ,case when Docu.REFERENCE like 'IPC%' then TOTAL else  0 end as TMP_PLAN
				from DOCU_PROD DP
				join DOCUMENTS Docu on DP.K_DOCUMENT=Docu.K_DOCUMENT
				where DP.K_PRODUCT=53 and DP.F_LOT=64
		) as src
	group by K_PRODUCT

Petit exemple :
Nom : result.PNG
Affichages : 1118
Taille : 13,0 Ko

**aieeeuuuuu** · 22/08/2018, 11h16

Bonjour,

Votre requête en l'état est très bien.

Avez-vous des problèmes de performances ? (il pourrait alors être nécessaire d'indexer)

Votre modèle en revanche est une abomination.
Il eut fallut éviter le type float pour les clefs primaires
de plus, le type varchar(1) est presque un non sens. Il occupera 2 à 3 octets par ligne, là ou un char(1) n'en occupera qu'un seul.
il y aurait bien d’autres choses à dire sur le modèle, mais comme visiblement vous ne pouvez le modifier...

**Firlfire** · 22/08/2018, 14h17

Bonjour,

Je suis entièrement d'accord pour le modèle (pour ça que j'ai précisé que je ne pouvais pas le modifier), seulement je fais seulement de la customisation de l'appli (fichier de dev, pas de la structure de la DB)...

Donc pour la requête il n'y a pas "d'amélioration(s)" ?
Non je n'ai pas de problème de temps d'éxécution, surtout qu'elle ne devrait retourner qu'une seule ligne sauf si on lui passe le mauvais K_PRODUCT

Comme vous avez put le voir j'avais essayer d'utiliser un pivot ("sub-query = 2 colonnes et 2 lignes, puis pivot sur la 1ere colonne) mais comme je bloquais j'ai fait autrement.

Je suis un peu perdu quant à l'utilisation du PIVOT : dans quel cas est-ce plus intéressant/pratique d'utiliser un PIVOT qu'une sous-requête dans le from ?

**aieeeuuuuu** · 22/08/2018, 15h33

Notez que la sous requête n'est pas obligatoire, on peut directement pivoter ainsi :

Code :

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

1
2
3
4
5
6
7
8
 
SELECT DP.K_PRODUCT
	   , SUM(CASE WHEN Docu.REFERENCE like 'D%' THEN TOTAL ELSE  0 END) AS V_PREV
	   , SUM(CASE WHEN Docu.REFERENCE like 'IPC%' THEN TOTAL ELSE  0 END) AS V_PLAN
FROM DOCU_PROD DP
JOIN DOCUMENTS Docu on DP.K_DOCUMENT=Docu.K_DOCUMENT
WHERE DP.K_PRODUCT=53 and DP.F_LOT=64
GROUP BY K_PRODUCT

C'est la version à l'ancienne du pivot, avant que l'opérateur ne soit implémenté dans les SGBDR

Personnellement, je n'ai jamais vu de différence de performances (ni même de plan d'exécution) entre les deux façons de faire (à l'ancienne ou avec PIVOT) sur SQL Server.

(il me semble que j'en ai toutefois vu pour l'opération inverse UNPIVOT par rapport à ses versions à l'ancienne).

Cela dit, je n'ai que très peu utilisé ces opérateurs...

**Firlfire** · 22/08/2018, 17h05

Bonjour,

Pour la 1ère requête ça devrait aller, en revanche je viens de faire se trouvant en bas du post.
Pour l'instant je suis en phase de test : seulement 4 Document donc aucun soucis.

Je suis novice en administration et optimisation SGBD, ce qui m'inquiète c'est sur la durée : cette requête traite toutes les lignes de DOCU$INVOICING où REFERENCE like 'D%' ou 1 avec REFERENCE like 'IPC%' d'il y a 10 ans a l'année courante.
DOCU$INVOICING garde des informations de type Facture, donc à chaque fois qu'il y a un Devis, un Avenant, une Commande, [...] il y aura une nouvelle ligne dans ces tables, du coup c'est typiquement le genre de table qui peut très vite monter en volume (donc nombre de lignes a traiter).

Le screenshot est juste la pour montrer les "transformations" que la requête effectue (1er tableau = cte_base_data, 2ème tableau = cte_cesure_data, tableau 3 = résultat avec PIVOT
Nom : CAByYear.PNG
Affichages : 1119
Taille : 49,4 Ko

Nom : CAByYear.PNG
Affichages : 1119
Taille : 49,4 Ko

Code SQL :

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
with cte_base_data as (
	select inv.K_DOCUMENT
		  ,case
			when Docu.REFERENCE like 'D%' then 'PRE'
			when Docu.REFERENCE like 'IPC%' then 'CON'
		   END as REFERENCE
		  ,inv.D_INVOICE
		  ,inv.TOTAL_NO_VAT
		  ,inv.F_CESURE1
		  ,inv.F_CESURE2
		  ,inv.F_CESURE3
		  ,inv.F_CESURE4
		from DOCU$INVOICING inv
		join DOCUMENTS Docu on Docu.K_DOCUMENT=inv.K_DOCUMENT and (Docu.REFERENCE like 'D%' or Docu.REFERENCE like 'IPC%') and Year(inv.D_INVOICE)>=Year(Getdate())-10
		where Docu.K_USER=2
),
cte_cesure_data (annee, type, montant) as (
	select year(D_INVOICE) + case cesure when 'F_CESURE1' then 0 when 'F_CESURE2' then 1 when 'F_CESURE3' then 2 when 'F_CESURE4' then 3 end
		  ,REFERENCE
		  ,sum(TOTAL_NO_VAT * pct) / 100.00
		  	from cte_base_data unpivot (pct for cesure in (F_CESURE1, F_CESURE2, F_CESURE3, F_CESURE4)) as pvt
		  	group by year(D_INVOICE) + case cesure when 'F_CESURE1' then 0 when 'F_CESURE2' then 1 when 'F_CESURE3' then 2 when 'F_CESURE4' then 3 end, REFERENCE
)
 
select annee, [PRE], [CON]
	from (select * from cte_cesure_data) mnt
	pivot (Sum(montant) for type in ([PRE], [CON])) as pvt

**aieeeuuuuu** · 22/08/2018, 17h29

L'avantage de la solution à l'ancienne, c'est que vous pouvez en faire une vue indexée sous SQL Server, afin de résoudre les problèmes de perf éventuels si la volumétrie est importante (attention, ça peut néanmoins avoir un impact négatif sur la mise à jour des tables sous-jacentes)

PIVOT 2 lignes en 2 colonne suivant valeur d'un autre champ

Développement SQL Server

Discussions similaires

Partager

Partager