Modifier la structure d'une table avec les Procédures Stockées

Question

Salut;
j'ai un table de pointage sous une base de données MS sql server 2005 avec la structure suivante:
Pointage(matric,date,heure)
matric:le matricule de l'employé de type int
date  :le date de pointage(2012/07/18)
heure :l'heure de pointage(12:50:51)
Je veux transformer la structure du table avec une procédure stocké pour une bonne lisibilité sous la forme suivante:

Pointage(matric,date,P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15,P16)
avec Pi est l'heure de pointage qu'un employé fait pour un date donnée
Un employé pointe plusieurs fois (max 16 fois)par jour

quelqu'un peut m'aider et merci d'avance

cs_jopop · Answer

Salut,

je n'ai pas de réponse mais une question : pourquoi faire une procédure stockée pour un ALTER TABLE que tu ne feras qu'une fois ? La lisibilité ne rentrant absolument pas en compte puisque tu auras une table "finie".

meherzayani · Answer

@jopop
je veux juste que pour chaque jour(date)avoir les pointages faite par chaque employé

cs_jopop · Answer

Pour ça il y a le INSERT INTO, et pareil, pas besoin de procédure stockée dans l'absolue (à part si t'as des règles de gestion très particulières). Et du coup tu ne cherches pas à modifier la structure de la table ...

Je sais pas si ton analyse est aussi confuse que les explications que tu donnes ici. Perso quand je comprends pas le logique je creuse pas plus. Aider à développer des raisonnements bancals c'est pas ma philosophie.

cs_Malkuth · Answer

Le plus simple c'est de passer par un pivot et une fonction fenêtré, tu peux ce faisant te passer de procédure stocker ou autres artifices.

pour commencé, on va numéroter les lignes

on as par exemple:
Emp1 2013-01-01 10:01
Emp2 2013-01-01 10:53
Emp1 2013-01-01 11:45
Emp2 2013-01-01 12:10
Emp1 2013-01-02 09:37
Emp1 2013-01-02 11:58
Emp1 2013-01-02 13:10
Emp1 2013-01-02 17:53

on veux numéroté les lignes dans l'ordre croissant des heures pour chaque couple date/employé :
Emp1 2013-01-01 10:01 1
Emp1 2013-01-01 11:45 2
Emp1 2013-01-02 09:37 1
Emp1 2013-01-02 11:58 2
Emp1 2013-01-02 13:10 3
Emp1 2013-01-02 17:53 4
Emp2 2013-01-01 10:53 1
Emp2 2013-01-01 12:10 2

pour se faire on utilise la function ROW_NUMBER() OVER(...)

SELECT 
    [matric],
    [date],
    [heure],
    ROW_NUMBER() OVER (PARTITION BY [matric],[date] ORDER BY [heure]) AS P
FROM [dbo].[Pointage]

Ensuite on va pivoter le résultat de cette première requette, les numéros qu'on viens de calculer vont devenir des noms de colonnes qui contiendrons l&#8217;horaire associé, en groupant le tout suivant les autres champs de la requette,

SELECT
[PVT].[matric],
[PVT].[date],
[PVT].[1] AS [P1],
[PVT].[2] AS [P2],
[PVT].[3] AS [P3],
[PVT].[4] AS [P4],
[PVT].[5] AS [P5],
[PVT].[6] AS [P6],
[PVT].[7] AS [P7],
[PVT].[8] AS [P8],
[PVT].[9] AS [P9],
[PVT].[10] AS [P10],
[PVT].[11] AS [P11],
[PVT].[12] AS [P12],
[PVT].[13] AS [P13],
[PVT].[14] AS [P14],
[PVT].[15] AS [P15],
[PVT].[16] AS [P16]
FROM (
SELECT 
[matric],
[date],
[heure],
ROW_NUMBER() OVER (PARTITION BY [matric],[date] ORDER BY [heure]) AS P
FROM [dbo].[Pointage]
) AS [SRC]
PIVOT (
MIN([heure])
FOR [P] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16])
) AS [PVT]

Pour amélioré la rapidité de la requette, un index sur la table est le bienvenue (surtout si elle grossi bien...) :

CREATE NONCLUSTERED INDEX [IX_Pivot_Helper] ON [dbo].[Pointage] 
(
[matric] ASC,
[date] ASC,
[heure] ASC
)

Pour finir, si tu ne veux pas réécrire cette requette pense à en faire une Vue :

CREATE VIEW [Pointage_Pivoter] AS
SELECT
[PVT].[matric],
[PVT].[date],
[PVT].[1] AS [P1],
[PVT].[2] AS [P2],
[PVT].[3] AS [P3],
[PVT].[4] AS [P4],
[PVT].[5] AS [P5],
[PVT].[6] AS [P6],
[PVT].[7] AS [P7],
[PVT].[8] AS [P8],
[PVT].[9] AS [P9],
[PVT].[10] AS [P10],
[PVT].[11] AS [P11],
[PVT].[12] AS [P12],
[PVT].[13] AS [P13],
[PVT].[14] AS [P14],
[PVT].[15] AS [P15],
[PVT].[16] AS [P16]
FROM (
SELECT 
[matric],
[date],
[heure],
ROW_NUMBER() OVER (PARTITION BY [matric],[date] ORDER BY [heure]) AS P
FROM [dbo].[Pointage]
) AS [SRC]
PIVOT (
MIN([heure])
FOR [P] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16])
) AS [PVT]

Tu n'auras plus qu'as utilisé la requette suivante :

 SELECT * FROM [Pointage_Pivoter] WHERE [matric] = 'Emp1'

Modifier la structure d'une table avec les Procédures Stockées

4 réponses

Votre réponse

Discussions similaires