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>
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>
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>
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>
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 )" <aa@aa.fr> a écrit dans le message de groupe de discussion :
uMFiqYoZIHA.4208@TK2MSFTNGP04.phx.gbl...
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>
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>
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>
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 )" <aa@aa.fr> a écrit dans le message de groupe de discussion :
uMFiqYoZIHA.4208@TK2MSFTNGP04.phx.gbl...
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>
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>
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>
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]" <ptrotin@online.microsoft.com> a écrit dans le
message de news: 2EEC47E9-7391-4C32-AC42-31D0EFFC0317@microsoft.com...
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 )" <aa@aa.fr> a écrit dans le message de groupe de discussion
: uMFiqYoZIHA.4208@TK2MSFTNGP04.phx.gbl...
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>
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>
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>
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]" <ptrotin@online.microsoft.com> a écrit dans le
message de news:2EEC47E9-7391-4C32-AC42-31D0EFFC0317@microsoft.com...
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 )" <aa@aa.fr> a écrit dans le message de groupe de discussion
: uMFiqYoZIHA.4208@TK2MSFTNGP04.phx.gbl...
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>
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>