Discussion :

[Mawa31]

Bonjour,

j'ai besoin de vos aide concernant un tableau croisé en sql server en utilisant la commande PIVOT avec sql server:

1) mon besoin est de convertir la durée en secondes en format time hh:mm:ss.
2) comment calculer les totaux par colonnes et par lignes du tableau pivot.

NB: j'ai essayé de convertir ma durée en secondes:

Code :

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

CONVERT(varchar, DATEADD(ss, SUM( dureeEnSecondes), 0), 108) AS Duration

, çà fonctionne bien dans ma SELECT, mais quand je le mets dans fonction d'agrégation SUM sous pivot, çà fonctionne pas.

ma requête est ci-dessous:

Code :

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

1
2
3
4
5
6
7
8
select * from 
(
select  categorieAr,sujet, dureeEnSecondes
from dbo.listeSequence
where etat ='v' and dateDebut >= '2022-01-01') as t 
pivot (
SUM(dureeEnSecondes) for sujet in ([neutre],[pour],[contre])
) as p

voilà les resultat de ma requete, je voudrais mettre la duree en TIME et ajouter les totaux colonnes et lignes , Comment faire?

CATEGORIES	NEUTRE	POUR	CONTRE
C1	45875	642	NULL
C2	455	642	50
C3	NULL	NULL	50

[sqllm]

Bonjour

Ca va être un usine à gaz car Sql n’est pas fait pour ca … Sqlpro a ecrit un blog à ce sujet de mémoire.

Je me suis quand même amusé à essayer de trouver une réponse, voici un exemple de solution

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
with tmp as (select 'C1' as cat, 'N' as rep, 45000 as nb union all
select 'C1' as cat, 'N' as rep, 875 as nb union all
select 'C1' as cat, 'P' as rep, 600 as nb union all
select 'C1' as cat, 'P' as rep, 42 as nb union all
select 'C2' as cat, 'N' as rep, 455 as nb union all
select 'C2' as cat, 'P' as rep, 640 as nb union all
select 'C2' as cat, 'P' as rep, 2 as nb union all
select 'C2' as cat, 'C' as rep, 50 as nb union all
select 'C3' as cat, 'C' as rep, 50 as nb )
, tmp2 as (
select coalesce(cat, 'Total') as cat, 
sum(coalesce(N,0)) as N,
sum(coalesce(P,0)) as P, sum(coalesce(C,0)) as C
, sum(coalesce(N,0) +coalesce(P,0) +coalesce(C,0)) as Total
 
from ( select cat, rep, nb from tmp ) as piv  pivot 
(sum(nb) for rep in ([N], [P], [C])) as p
group by cat with rollup)
 
 SELECT  cat, Right('0' + CAST(N / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((N / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(N % 60 AS VARCHAR),2) as N
, Right('0' + CAST(P / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((P / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(P % 60 AS VARCHAR),2) as P
, Right('0' + CAST(C / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((C / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(C % 60 AS VARCHAR),2) as C
, Right('0' + CAST(Total / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((Total / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(Total % 60 AS VARCHAR),2) as Total
 
from tmp2

Pour résultat

cat	N	P	C	Total
C1	12:44:35	00:10:42	00:00:00	12:55:17
C2	00:07:35	00:10:42	00:00:50	00:19:07
C3	00:00:00	00:00:00	00:00:50	00:00:50
Total	12:52:10	00:21:24	00:01:40	13:15:14

Attention aux conversions d’heures, je laisse les experts répondre mais à ma connaissance sql ne sait pas formater des heures > 24. C’est pour ca que je convertis manuellement. Il faudra adapter si le nombre d’heures peut être >99

[SQLpro]

Pour ta conversion HMS :

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
CREATE OR ALTER FUNCTION FN_CONVERT_HD_HMS (@HD FLOAT) 
RETURNS VARCHAR(32)
WITH RETURNS NULL ON NULL INPUT
AS 
BEGIN
 
DECLARE @H INTEGER, @M INTEGER, @S INTEGER;
 
-- récupération des heures, minutes, secondes
SET @H  = FLOOR(@HD)
SET @HD = @HD - @H
SET @HD = @HD * 60
SET @M  = FLOOR(@HD)
SET @HD = @HD - @M
SET @HD = @HD * 60
SET @S  = FLOOR(@HD)
 
RETURN CASE WHEN @H < 10 THEN '0'+CAST(@H AS CHAR(1)) ELSE CAST(@H AS VARCHAR(16)) END
            + ':' + FORMAT(@M, '00') + ':' + FORMAT(@S, '00');
 
END
GO

Exemple :

Code :

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

1
2
3
4
5
6
 
SELECT dbo.FN_CONVERT_HD_HMS(12345.125) AS HMS
 
HMS
--------------------------------
12345:07:30

A +

[sqllm]

Super !

merci pour l'info et bravo pour ton bouquin qui est une mine d'or.

[Mawa31]

Bonjour et merci infiniment pour vos réponses et vos efforts de résoudre ce problème.

Le temps que j'ai attendu l'aide dans ce forum, j'ai essayé de me débrouiller un peu aves mes requêtes SQL, et voilà ma requête qui peut me donner les TOTALS ROWS and COLS, ce qui me manque juste la conversion, je vais essayer avec les solutions que vous m'avez donné.

Ci-dessous ma SOLUTION qui peut être aider quelqu'un d'autre:

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
--BEGIN dynamique pivot 
declare @tot varchar(max)= ',[TOTAL]';
declare @var varchar(max)='';
select @var+=quotename(role)+',' 
from [dbo].[listeSequence]
where etat ='v' and cast(dateDebut as date) between '2022-01-10' and '2022-01-16'
group by role --list des roles 
set @var = SUBSTRING(@var,1,len(@var)-1)
set @var=@var+@tot;
--print @var;
 
declare @req varchar(max);
set @req ='select * from 
(
select  COALESCE(categorie,''TOTAL'') as categorie,
COALESCE(role, ''TOTAL'') as role,
sum(dureeEnSecondes) as dureeEnSecondes
from dbo.listeSequence
where etat =''v'' and cast(dateDebut as date)between ''2022-01-10'' and ''2022-01-16'' GROUP BY CUBE (categorie, role)) as t 
pivot (
SUM(dureeEnSecondes) for role in ('+@var+')
) as p';
exec (@req);
--FIN dynamique pivot

Résultat:

categories	Role1	Role2	Role3	TOTAL
C1	175	80	NULL	255
C2	NULL	120	NULL	120
C3	100	120	30	250
TOTAL	275	320	30	625

Merci encore.
A+