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

Le
\( 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 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>
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
Philippe TROTIN [MS]
Le #11882931
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 )"
Bonjour,

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>



olivier
Le #11882921
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]" 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 )"
Bonjour,

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>





\( Olivier \)
Le #11882891
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]" 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 )"
Bonjour,

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>





olivier
Le #11882881
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"
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]" 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 )" :
Bonjour,

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>









olivier
Le #11882871
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 )"
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]" 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 )" :
Bonjour,

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>








Publicité
Poster une réponse
Anonyme