PDA

View Full Version : ایجاد لیست حضور و غیاب با استفاده از Crosstab / Pivot



pcseven
یک شنبه 15 اسفند 1389, 19:19 عصر
سلام
دیتابیسی با ساختار زیر داریم:

جدول دانشجویان

Student_Name Student_ID
علی 1
سعید 2
سارا 3
محمد 4

جدول دروس

Course_ID Course_Title
1 زبان
2 ریاضی
3 فیزیک

جدول جلسات برگزار شده

Session_ID Session_CourseID Session_Date
1 1 89/11/03
2 1 89/11/10
3 2 89/10/15
4 2 89/10/22
5 2 89/10/29

جدول حضور دانشجویان در جلسات برگزار شده

Attendance_ID Attendance_SessionID Attendance_StudentID
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 2 3
7 2 4



چنانچه بخواهیم یک لیست حضور و غیاب به شکل زیر ایجاد کنیم، کوئری مورد نیاز چگونه خواهد بود؟(با فرض اینکه تاریخ جلسات مشخص نیست و باید داینامیک باشد)

Pivot :
لیست حضور و غیاب درس زبان

89/11/03 89/11/10 …
علی P P
سعید P A
سارا P P
محمد P P

Reza_Yarahmadi
یک شنبه 15 اسفند 1389, 22:21 عصر
فکر کنم نمونه کد زیر بتونه کمکتون کنه.
CREATE TABLE Stu (
Student_Name nvarchar(max),
Student_ID int)

INSERT INTO Stu VALUES (N'علی', 1)
INSERT INTO Stu VALUES (N'سعید', 2)
INSERT INTO Stu VALUES (N'سارا', 3)
INSERT INTO Stu VALUES (N'محمد', 4)

CREATE TABLE Course (
Course_Title nvarchar(max),
Course_ID int)

INSERT INTO Course VALUES (N'زبان', 1)
INSERT INTO Course VALUES (N'ریاضی', 2)
INSERT INTO Course VALUES (N'فیزیک', 3)

CREATE TABLE Session (
Session_ID int,
Session_CourseID int,
Session_Date varchar(10))

INSERT INTO Session VALUES (1, 1, '89/11/03')
INSERT INTO Session VALUES (2, 1, '89/11/10')
INSERT INTO Session VALUES (3, 2, '89/10/15')
INSERT INTO Session VALUES (4, 2, '89/10/22')
INSERT INTO Session VALUES (5, 2, '89/10/29')

CREATE TABLE Attendance (
Attendance_ID int,
Attendance_SessionID int,
Attendance_StudentID int)

INSERT INTO Attendance VALUES (1, 1, 1)
INSERT INTO Attendance VALUES (2, 1, 2)
INSERT INTO Attendance VALUES (3, 1, 3)
INSERT INTO Attendance VALUES (4, 1, 4)
INSERT INTO Attendance VALUES (5, 2, 1)
INSERT INTO Attendance VALUES (6, 2, 3)
INSERT INTO Attendance VALUES (7, 2, 4)

Declare @Str varchar(max)
Set @Str = ''
Select @Str = @Str + Case When CharIndex(Session_Date, @Str) = 0 Then
Case When @Str <> '' Then ','
else '' End +
'[' + Session_Date + ']'
Else '' End
From Attendance A Inner Join Session S
On A.Attendance_SessionID = S.Session_ID

Set @Str = 'Select Student_Name, ' + @Str +'
FROM (
Select
Stu.Student_Name,
A.Attendance_ID,
S.Session_Date
From
Stu Left Outer Join Attendance A
On
Stu.Student_ID = A.Attendance_StudentID
Inner Join Session S
On
A.Attendance_SessionID = S.Session_ID) p
PIVOT
(Count(Attendance_ID)
For Session_Date IN (' + @Str + ')) as pvt'

Exec(@Str)

drop table Stu
drop table Course
drop table Session
drop table Attendance