Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11

Requête : Comment avoir par prof, le nombre de cours fait par mois :

5 réponses
Avatar
\( Olivier \)
Bonjour,
< script de création et des insert ci-dessous.>

comment avoir par prof, le nombre de cours fait par mois :
mon pb est que je ne sais comment mettre les mois en horizontal.

par exemple :

PROFS Jan Fev Mar etc...
----------------------------------------
Prof 1 10 25 32 ...
Prof 2 10 25 32 ...
Prof 3 10 25 32 ...
----------------------------------------
Total 30 75 96 ...

merci

--<Script>
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
02/03/2008 17:47:21 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\ecole.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\DATA\ecole_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB
, FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel=90
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
USE [ecole]
GO
/****** Objet : Table [dbo].[profs] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[profs](
[prof_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_profs] PRIMARY KEY CLUSTERED
(
[prof_id] 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
/****** Objet : Table [dbo].[eleves] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[eleve_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[eleve_id] 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
/****** Objet : Table [dbo].[matieres] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[matieres](
[matiere_id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](30) NULL,
CONSTRAINT [PK_lecons] PRIMARY KEY CLUSTERED
(
[matiere_id] 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
/****** Objet : Table [dbo].[cours] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[cour_id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[prof_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cour] [datetime] NULL,
CONSTRAINT [PK_cours] PRIMARY KEY CLUSTERED
(
[cour_id] 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



INSERT INTO eleves (nom) VALUES ('Toto')
INSERT INTO eleves (nom) VALUES ('Tata')
INSERT INTO eleves (nom) VALUES ('Titi')

INSERT INTO profs (nom) VALUES ('Prof 1')
INSERT INTO profs (nom) VALUES ('Prof 2')
INSERT INTO profs (nom) VALUES ('Prof 3')

INSERT INTO matieres (libelle) VALUES ('Anglais')
INSERT INTO matieres (libelle) VALUES ('Maths')
INSERT INTO matieres (libelle) VALUES ('Francais')

INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,3,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (3,1,GetDate())

--</script>

5 réponses

Avatar
Philippe TROTIN [MS]
Bonjour,

Avec la fonction PIVOT de SQL 2005, vous devriez vous en sortir.

Voici un exemple a adapter (fonctionne sur votre base) :

SELECT *
FROM (SELECT prof_id, eleve_id, MONTH(date_cour) AS month_id FROM cours) n
PIVOT( COUNT(eleve_id) FOR month_id IN ([1], [2], [3], [4], [5], [6], [7],
[8], [9], [10], [11], [12]) ) t


Cordialement
_______________________________

Philippe TROTIN
Microsoft Services France
_______________________________

"( Olivier )" a écrit dans le message de groupe de discussion :

Bonjour,
< script de création et des insert ci-dessous.>

comment avoir par prof, le nombre de cours fait par mois :
mon pb est que je ne sais comment mettre les mois en horizontal.

par exemple :

PROFS Jan Fev Mar etc...
----------------------------------------
Prof 1 10 25 32 ...
Prof 2 10 25 32 ...
Prof 3 10 25 32 ...
----------------------------------------
Total 30 75 96 ...

merci

--<Script>
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
02/03/2008 17:47:21 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
USE [ecole]
GO
/****** Objet : Table [dbo].[profs] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[profs](
[prof_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_profs] PRIMARY KEY CLUSTERED
(
[prof_id] 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
/****** Objet : Table [dbo].[eleves] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[eleve_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[eleve_id] 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
/****** Objet : Table [dbo].[matieres] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[matieres](
[matiere_id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](30) NULL,
CONSTRAINT [PK_lecons] PRIMARY KEY CLUSTERED
(
[matiere_id] 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
/****** Objet : Table [dbo].[cours] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[cour_id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[prof_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cour] [datetime] NULL,
CONSTRAINT [PK_cours] PRIMARY KEY CLUSTERED
(
[cour_id] 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



INSERT INTO eleves (nom) VALUES ('Toto')
INSERT INTO eleves (nom) VALUES ('Tata')
INSERT INTO eleves (nom) VALUES ('Titi')

INSERT INTO profs (nom) VALUES ('Prof 1')
INSERT INTO profs (nom) VALUES ('Prof 2')
INSERT INTO profs (nom) VALUES ('Prof 3')

INSERT INTO matieres (libelle) VALUES ('Anglais')
INSERT INTO matieres (libelle) VALUES ('Maths')
INSERT INTO matieres (libelle) VALUES ('Francais')

INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,3,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (3,1,GetDate())

--</script>



Avatar
olivier
super !

il me reste plus qu'a trouver comment rajouter une dernière colonne avec le
total annuel par profs.
ca doit etre du coté de Sum() ???

merci



"Philippe TROTIN [MS]" a écrit dans le
message de news:
Bonjour,

Avec la fonction PIVOT de SQL 2005, vous devriez vous en sortir.

Voici un exemple a adapter (fonctionne sur votre base) :

SELECT *
FROM (SELECT prof_id, eleve_id, MONTH(date_cour) AS month_id FROM cours) n
PIVOT( COUNT(eleve_id) FOR month_id IN ([1], [2], [3], [4], [5], [6], [7],
[8], [9], [10], [11], [12]) ) t


Cordialement
_______________________________

Philippe TROTIN
Microsoft Services France
_______________________________

"( Olivier )" a écrit dans le message de groupe de discussion :

Bonjour,
< script de création et des insert ci-dessous.>

comment avoir par prof, le nombre de cours fait par mois :
mon pb est que je ne sais comment mettre les mois en horizontal.

par exemple :

PROFS Jan Fev Mar etc...
----------------------------------------
Prof 1 10 25 32 ...
Prof 2 10 25 32 ...
Prof 3 10 25 32 ...
----------------------------------------
Total 30 75 96 ...

merci

--<Script>
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
02/03/2008 17:47:21 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
USE [ecole]
GO
/****** Objet : Table [dbo].[profs] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[profs](
[prof_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_profs] PRIMARY KEY CLUSTERED
(
[prof_id] 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
/****** Objet : Table [dbo].[eleves] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[eleve_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[eleve_id] 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
/****** Objet : Table [dbo].[matieres] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[matieres](
[matiere_id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](30) NULL,
CONSTRAINT [PK_lecons] PRIMARY KEY CLUSTERED
(
[matiere_id] 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
/****** Objet : Table [dbo].[cours] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[cour_id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[prof_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cour] [datetime] NULL,
CONSTRAINT [PK_cours] PRIMARY KEY CLUSTERED
(
[cour_id] 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



INSERT INTO eleves (nom) VALUES ('Toto')
INSERT INTO eleves (nom) VALUES ('Tata')
INSERT INTO eleves (nom) VALUES ('Titi')

INSERT INTO profs (nom) VALUES ('Prof 1')
INSERT INTO profs (nom) VALUES ('Prof 2')
INSERT INTO profs (nom) VALUES ('Prof 3')

INSERT INTO matieres (libelle) VALUES ('Anglais')
INSERT INTO matieres (libelle) VALUES ('Maths')
INSERT INTO matieres (libelle) VALUES ('Francais')

INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,3,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (3,1,GetDate())

--</script>





Avatar
\( Olivier \)
hum hum maintenant plus dur :

la même chose mais avec le nombre d'éléves "different" qu'ils ont vus au
moins une fois ?






"Philippe TROTIN [MS]" a écrit dans le
message de news:
Bonjour,

Avec la fonction PIVOT de SQL 2005, vous devriez vous en sortir.

Voici un exemple a adapter (fonctionne sur votre base) :

SELECT *
FROM (SELECT prof_id, eleve_id, MONTH(date_cour) AS month_id FROM cours) n
PIVOT( COUNT(eleve_id) FOR month_id IN ([1], [2], [3], [4], [5], [6], [7],
[8], [9], [10], [11], [12]) ) t


Cordialement
_______________________________

Philippe TROTIN
Microsoft Services France
_______________________________

"( Olivier )" a écrit dans le message de groupe de discussion :

Bonjour,
< script de création et des insert ci-dessous.>

comment avoir par prof, le nombre de cours fait par mois :
mon pb est que je ne sais comment mettre les mois en horizontal.

par exemple :

PROFS Jan Fev Mar etc...
----------------------------------------
Prof 1 10 25 32 ...
Prof 2 10 25 32 ...
Prof 3 10 25 32 ...
----------------------------------------
Total 30 75 96 ...

merci

--<Script>
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
02/03/2008 17:47:21 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
USE [ecole]
GO
/****** Objet : Table [dbo].[profs] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[profs](
[prof_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_profs] PRIMARY KEY CLUSTERED
(
[prof_id] 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
/****** Objet : Table [dbo].[eleves] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[eleve_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[eleve_id] 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
/****** Objet : Table [dbo].[matieres] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[matieres](
[matiere_id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](30) NULL,
CONSTRAINT [PK_lecons] PRIMARY KEY CLUSTERED
(
[matiere_id] 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
/****** Objet : Table [dbo].[cours] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[cour_id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[prof_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cour] [datetime] NULL,
CONSTRAINT [PK_cours] PRIMARY KEY CLUSTERED
(
[cour_id] 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



INSERT INTO eleves (nom) VALUES ('Toto')
INSERT INTO eleves (nom) VALUES ('Tata')
INSERT INTO eleves (nom) VALUES ('Titi')

INSERT INTO profs (nom) VALUES ('Prof 1')
INSERT INTO profs (nom) VALUES ('Prof 2')
INSERT INTO profs (nom) VALUES ('Prof 3')

INSERT INTO matieres (libelle) VALUES ('Anglais')
INSERT INTO matieres (libelle) VALUES ('Maths')
INSERT INTO matieres (libelle) VALUES ('Francais')

INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,3,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (3,1,GetDate())

--</script>





Avatar
olivier
bon j'ai essayé mais c'est pas terrible :

select count(distinct eleve_id) as 'eleve' , prof_id, month(date_cour) as
month_id
from cours
group by prof_id, month(date_cour)

--
==================================================================================== SELECT *
FROM (
select count(distinct eleve_id) as 'eleve' , prof_id, month(date_cour) as
month_id
from cours
group by prof_id, month(date_cour) )n
PIVOT( COUNT(eleve) FOR month_id IN ([1], [2], [3], [4], [5], [6], [7], [8],
[9], [10], [11], [12]) ) t





"olivier" a écrit dans le message de news:

super !

il me reste plus qu'a trouver comment rajouter une dernière colonne avec
le total annuel par profs.
ca doit etre du coté de Sum() ???

merci



"Philippe TROTIN [MS]" a écrit dans le
message de news:
Bonjour,

Avec la fonction PIVOT de SQL 2005, vous devriez vous en sortir.

Voici un exemple a adapter (fonctionne sur votre base) :

SELECT *
FROM (SELECT prof_id, eleve_id, MONTH(date_cour) AS month_id FROM cours)
n
PIVOT( COUNT(eleve_id) FOR month_id IN ([1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12]) ) t


Cordialement
_______________________________

Philippe TROTIN
Microsoft Services France
_______________________________

"( Olivier )" a écrit dans le message de groupe de discussion
:
Bonjour,
< script de création et des insert ci-dessous.>

comment avoir par prof, le nombre de cours fait par mois :
mon pb est que je ne sais comment mettre les mois en horizontal.

par exemple :

PROFS Jan Fev Mar etc...
----------------------------------------
Prof 1 10 25 32 ...
Prof 2 10 25 32 ...
Prof 3 10 25 32 ...
----------------------------------------
Total 30 75 96 ...

merci

--<Script>
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
02/03/2008 17:47:21 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
USE [ecole]
GO
/****** Objet : Table [dbo].[profs] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[profs](
[prof_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_profs] PRIMARY KEY CLUSTERED
(
[prof_id] 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
/****** Objet : Table [dbo].[eleves] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[eleve_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[eleve_id] 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
/****** Objet : Table [dbo].[matieres] Date de génération du script
: 02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[matieres](
[matiere_id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](30) NULL,
CONSTRAINT [PK_lecons] PRIMARY KEY CLUSTERED
(
[matiere_id] 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
/****** Objet : Table [dbo].[cours] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[cour_id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[prof_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cour] [datetime] NULL,
CONSTRAINT [PK_cours] PRIMARY KEY CLUSTERED
(
[cour_id] 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



INSERT INTO eleves (nom) VALUES ('Toto')
INSERT INTO eleves (nom) VALUES ('Tata')
INSERT INTO eleves (nom) VALUES ('Titi')

INSERT INTO profs (nom) VALUES ('Prof 1')
INSERT INTO profs (nom) VALUES ('Prof 2')
INSERT INTO profs (nom) VALUES ('Prof 3')

INSERT INTO matieres (libelle) VALUES ('Anglais')
INSERT INTO matieres (libelle) VALUES ('Maths')
INSERT INTO matieres (libelle) VALUES ('Francais')

INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,3,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (3,1,GetDate())

--</script>









Avatar
olivier
c'était plutot là le post ;-)

bon j'ai essayé mais c'est pas terrible :

select count(distinct eleve_id) as 'eleve' , prof_id, month(date_cour) as
month_id
from cours
group by prof_id, month(date_cour)

--
==================================================================================== SELECT *
FROM (
select count(distinct eleve_id) as 'eleve' , prof_id, month(date_cour) as
month_id
from cours
group by prof_id, month(date_cour) )n
PIVOT( COUNT(eleve) FOR month_id IN ([1], [2], [3], [4], [5], [6], [7], [8],
[9], [10], [11], [12]) ) t





"( Olivier )" a écrit dans le message de news:

hum hum maintenant plus dur :

la même chose mais avec le nombre d'éléves "different" qu'ils ont vus au
moins une fois ?






"Philippe TROTIN [MS]" a écrit dans le
message de news:
Bonjour,

Avec la fonction PIVOT de SQL 2005, vous devriez vous en sortir.

Voici un exemple a adapter (fonctionne sur votre base) :

SELECT *
FROM (SELECT prof_id, eleve_id, MONTH(date_cour) AS month_id FROM cours)
n
PIVOT( COUNT(eleve_id) FOR month_id IN ([1], [2], [3], [4], [5], [6],
[7], [8], [9], [10], [11], [12]) ) t


Cordialement
_______________________________

Philippe TROTIN
Microsoft Services France
_______________________________

"( Olivier )" a écrit dans le message de groupe de discussion
:
Bonjour,
< script de création et des insert ci-dessous.>

comment avoir par prof, le nombre de cours fait par mois :
mon pb est que je ne sais comment mettre les mois en horizontal.

par exemple :

PROFS Jan Fev Mar etc...
----------------------------------------
Prof 1 10 25 32 ...
Prof 2 10 25 32 ...
Prof 3 10 25 32 ...
----------------------------------------
Total 30 75 96 ...

merci

--<Script>
USE [master]
GO
/****** Objet : Database [ecole] Date de génération du script :
02/03/2008 17:47:21 ******/
CREATE DATABASE [ecole] ON PRIMARY
( NAME = N'ecole', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole.mdf' , SIZE = 3072KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ecole_log', FILENAME = N'C:Program FilesMicrosoft SQL
ServerMSSQL.1MSSQLDATAecole_log.ldf' , SIZE = 1024KB , MAXSIZE =
2048GB , FILEGROWTH = 10%)
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'ecole', @new_cmptlevel
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [ecole].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [ecole] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [ecole] SET ANSI_NULLS OFF
USE [ecole]
GO
/****** Objet : Table [dbo].[profs] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[profs](
[prof_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_profs] PRIMARY KEY CLUSTERED
(
[prof_id] 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
/****** Objet : Table [dbo].[eleves] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eleves](
[eleve_id] [int] IDENTITY(1,1) NOT NULL,
[nom] [nchar](10) NULL,
CONSTRAINT [PK_eleves] PRIMARY KEY CLUSTERED
(
[eleve_id] 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
/****** Objet : Table [dbo].[matieres] Date de génération du script
: 02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[matieres](
[matiere_id] [int] IDENTITY(1,1) NOT NULL,
[libelle] [nchar](30) NULL,
CONSTRAINT [PK_lecons] PRIMARY KEY CLUSTERED
(
[matiere_id] 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
/****** Objet : Table [dbo].[cours] Date de génération du script :
02/03/2008 17:47:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[cours](
[cour_id] [int] IDENTITY(1,1) NOT NULL,
[matiere_id] [int] NULL,
[prof_id] [int] NULL,
[eleve_id] [int] NULL,
[date_cour] [datetime] NULL,
CONSTRAINT [PK_cours] PRIMARY KEY CLUSTERED
(
[cour_id] 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



INSERT INTO eleves (nom) VALUES ('Toto')
INSERT INTO eleves (nom) VALUES ('Tata')
INSERT INTO eleves (nom) VALUES ('Titi')

INSERT INTO profs (nom) VALUES ('Prof 1')
INSERT INTO profs (nom) VALUES ('Prof 2')
INSERT INTO profs (nom) VALUES ('Prof 3')

INSERT INTO matieres (libelle) VALUES ('Anglais')
INSERT INTO matieres (libelle) VALUES ('Maths')
INSERT INTO matieres (libelle) VALUES ('Francais')

INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (1,3,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,2,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (2,1,GetDate())
INSERT INTO cours (prof_id, eleve_id,date_cour) VALUES (3,1,GetDate())

--</script>