صورت مساله
فرض کنید جدولی داریم که مشخصات دانشجویان را دربر گرفته است و جدول دیگر مشخصات دروس و جدولی دیگر دروس انتخاب شده توسط دانشجویان را نگه داری می کند. جدول دانشجو با جدول دروس انتخاب شده یک ارتباط 1 به چند (1-N) دارند و جدول داشنجو با جدول دروس یک ارتباط چند به چند دارند. در واقع جدول "دانشجو درس" جدول اتصال یا Junction Table نام دارد.
CREATE TABLE Students
(student_nbr INT IDENTITY(1, 1) PRIMARY KEY,
student_name VARCHAR(25) NOT NULL);
CREATE TABLE Courses
(course_nbr INT IDENTITY(1, 1) PRIMARY KEY,
course_name VARCHAR(25));
CREATE TABLE StudentCourse
(student_nbr INT NOT NULL
REFERENCES Students (student_nbr),
course_nbr INT NOT NULL
REFERENCES Courses (course_nbr),
PRIMARY KEY (student_nbr, course_nbr));
بعد از اجرای دستورات DDL فوق دیاگرام مربوطه به صورت زیر در خواهد آمد:
INSERT INTO Students (student_name)
VALUES ('David'),
('Petter'),
('Joe');
INSERT INTO Courses (course_name)
VALUES ('Computer Science'),
('Mathmatics'),
('English Language'),
('Database Conceps'),
('Advances SQL Programming');
INSERT INTO StudentCourse (student_nbr, course_nbr)
VALUES (1, 1), (1, 2), (1, 3),
(2, 1), (2, 4), (2, 5);
و پس از اجرای دستورات DML فوق و SELECT گرفتن از داده های هر سه جدول نتیجه ی زیر حاصل می شود:
داده های فوق به مدد یک SELECT ساده از جدول حاصل شده اند. این فرمت نمایش، نمی تواند رضایت خاطر کاربرانی که با این داده ها دائما در ارتباط هستند را بر آورده کند. پس برای نمایش ناچاریم که برای هر دانشجو تنها یک سطر در نظر گرفته و تمام دروس انتخابی آن را در یک ستون الحاق کنیم و با کاراکتری این دروس را از هم تفکیک کرده و به نمایش در آوریم.
پس نتیجه ی دلخواه و مورد نظر ما اینگونه می باشد:
این مساله ای که معرفی کردم به الحاق سطرهای جدول (Concatenating rows) شهرت دارد.
دو سناریو کلی وجود دارد. اولین سناریو این است که تعداد سطرهایی که باید الحاق شوند کم و مشخص است. در مثال ما هر دانشجو تنها سه درس انتخاب کرده است پس می توانیم این نیاز را با روشهای مجموعه گرا به سادگی حل کنیم. پشت ایده ی این روشهای مجموعه گرا ایده ی Cross Tabbing قرار دارد. پس تمام روشهای که برای حل مساله ی Cross tabbing وجود دارد را با کمی تغییر، برای این نیاز می توانیم استفاده کنیم.
سناریوی دوم این است که تعداد دروس هر دانشجو معلوم نبوده. که مخصوص این سناریو هم روش های متنوعی وجود دارد که دو روش از همه رایج تر و شناخته تر هستند. یعنی UDF و FOR XML PATH (قابل استفاده در نسخه ی 2005).
این خیلی مهم است که بگویم برخی از روش هایی که پیشنهاد داده شده اند تنها به خاطر کمال یافتن این مقاله است و توصیه نمی شود در سیستم محصولتان از آن استفاده کنید.
FOR XML PATH
--Using Correlated Subquery
SELECT S.student_name,
STUFF((SELECT ', ' + C.course_name
FROM StudentCourse SC
INNER JOIN Courses C
ON SC.course_nbr = C.course_nbr
WHERE SC.student_nbr = S.student_nbr
ORDER BY C.course_name ASC
FOR XML PATH('')), 1, 2, '') AS course_list
FROM Students AS S
ORDER BY student_name;
--Using CROSS APPLY
SELECT S.student_name, COALESCE(LEFT(D.list, LEN(D.list)-1), '') AS course_list
FROM Students AS S
CROSS APPLY (SELECT C.course_name + ', '
FROM StudentCourse SC
INNER JOIN Courses C
ON SC.course_nbr = C.course_nbr
WHERE SC.student_nbr = S.student_nbr
ORDER BY C.course_name ASC
FOR XML PATH('')) D(list)
ORDER BY student_name;
Recursive CTE
--Recursive CTE
;WITH Ranking AS
(SELECT S.student_nbr,
S.student_name,
C.course_name,
rec_id = ROW_NUMBER() OVER(PARTITION BY S.student_nbr ORDER BY C.course_name)
FROM StudentCourse SC
INNER JOIN Courses C
ON SC.course_nbr = C.course_nbr
INNER JOIN Students S
ON SC.student_nbr = S.student_nbr)
, Concatenating AS
(SELECT student_nbr, student_name, CAST(course_name AS VARCHAR(8000)) AS list, rec_id
FROM Ranking
WHERE rec_id = 1
UNION ALL
SELECT R.student_nbr, R.student_name, C.list +', '+ R.course_name AS list, R.rec_id
FROM Concatenating C
JOIN Ranking R
ON C.student_nbr = R.student_nbr
AND C.rec_id + 1 = R.rec_id)
SELECT student_name, MAX(list) AS List
FROM Concatenating
GROUP BY student_nbr, student_name
UNION ALL
SELECT student_name, ''
FROM students S
WHERE NOT EXISTS
(SELECT *
FROM StudentCourse
WHERE student_nbr = S.student_nbr);
CASE Expression
SELECT student_name,
MAX(CASE WHEN rec_id = 1 THEN course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 2 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 3 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 4 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 5 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 6 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 7 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 8 THEN ', ' + course_name ELSE '' END) +
MAX(CASE WHEN rec_id = 9 THEN ', ' + course_name ELSE '' END) AS list
FROM (SELECT S.student_nbr, S.student_name, C.course_name, D.i AS rec_id
FROM StudentCourse SC
INNER JOIN Courses C
ON SC.course_nbr = C.course_nbr
INNER JOIN Students S
ON SC.student_nbr = S.student_nbr
CROSS APPLY (SELECT COUNT(*)
FROM StudentCourse SC_1
JOIN Courses C_1
ON SC_1.course_nbr = C_1.course_nbr
WHERE student_nbr = SC.student_nbr
AND course_name <= C.course_name) D(i))D
GROUP BY student_nbr, student_name
UNION ALL
SELECT student_name, ''
FROM students S
WHERE NOT EXISTS
(SELECT *
FROM StudentCourse
WHERE student_nbr = S.student_nbr);
Multile Outer Joins
;WITH Ranking AS
(SELECT SC.student_nbr, C.course_name,
ROW_NUMBER() OVER(PARTITION BY SC.student_nbr ORDER BY C.course_name) AS row_id
FROM StudentCourse SC
INNER JOIN Courses C
ON SC.course_nbr = C.course_nbr),
rec_1 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 1),
rec_2 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 2),
rec_3 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 3),
rec_4 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 4),
rec_5 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 5),
rec_6 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 6),
rec_7 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 7),
rec_8 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 8),
rec_9 AS
(SELECT student_nbr, course_name
FROM Ranking
WHERE row_id = 9)
SELECT S.student_name,
List = COALESCE(r1.course_name , '')
+ COALESCE(', ' + r2.course_name , '')
+ COALESCE(', ' + r3.course_name, '')
+ COALESCE(', ' + r4.course_name, '')
+ COALESCE(', ' + r5.course_name, '')
+ COALESCE(', ' + r6.course_name, '')
+ COALESCE(', ' + r7.course_name, '')
+ COALESCE(', ' + r8.course_name, '')
+ COALESCE(', ' + r9.course_name, '')
FROM Students S
LEFT OUTER JOIN rec_1 r1
ON S.student_nbr = r1.student_nbr
LEFT OUTER JOIN rec_2 r2
ON S.student_nbr = r2.student_nbr
LEFT OUTER JOIN rec_3 r3
ON S.student_nbr = r3.student_nbr
LEFT OUTER JOIN rec_4 r4
ON S.student_nbr = r4.student_nbr
LEFT OUTER JOIN rec_5 r5
ON S.student_nbr = r5.student_nbr
LEFT OUTER JOIN rec_6 r6
ON S.student_nbr = r6.student_nbr
LEFT OUTER JOIN rec_7 r7
ON S.student_nbr = r7.student_nbr
LEFT OUTER JOIN rec_8 r8
ON S.student_nbr = r8.student_nbr
LEFT OUTER JOIN rec_9 r9
ON S.student_nbr = r9.student_nbr;
Multiple Self Joins
;WITH T AS
(SELECT student_nbr, C.course_name,
rec_id = ROW_NUMBER() OVER(PARTITION BY student_nbr ORDER BY course_name ASC)
FROM StudentCourse SC
JOIN Courses C
ON SC.course_nbr = C.course_nbr)
SELECT S.student_name
, MIN(List) AS List
FROM Students S
LEFT OUTER JOIN
(SELECT T1.student_nbr
, T1.course_name
+ COALESCE(', ' + T2.course_name, '')
+ COALESCE(', ' + T3.course_name, '')
+ COALESCE(', ' + T4.course_name, '')
+ COALESCE(', ' + T5.course_name, '')
+ COALESCE(', ' + T6.course_name, '')
+ COALESCE(', ' + T7.course_name, '')
+ COALESCE(', ' + T8.course_name, '')
+ COALESCE(', ' + T9.course_name, '') AS List
FROM T AS T1
LEFT JOIN T AS T2
ON T1.student_nbr = T2.student_nbr
AND T1.rec_id = T2.rec_id - 1
LEFT JOIN T AS T3
ON T2.student_nbr = T3.student_nbr
AND T2.rec_id = T3.rec_id - 1
LEFT JOIN T AS T4
ON T3.student_nbr = T4.student_nbr
AND T3.rec_id = T4.rec_id - 1
LEFT JOIN T AS T5
ON T4.student_nbr = T5.student_nbr
AND T4.rec_id = T5.rec_id - 1
LEFT JOIN T AS T6
ON T5.student_nbr = T6.student_nbr
AND T5.rec_id = T6.rec_id - 1
LEFT JOIN T AS T7
ON T6.student_nbr = T7.student_nbr
AND T6.rec_id = T7.rec_id - 1
LEFT JOIN T AS T8
ON T7.student_nbr = T8.student_nbr
AND T7.rec_id = T8.rec_id - 1
LEFT JOIN T AS T9
ON T8.student_nbr = T9.student_nbr
AND T8.rec_id = T9.rec_id - 1
) D
ON D.student_nbr = S.student_nbr
GROUP BY S.student_nbr, student_name;
دیگر روشهای موجود که مورد بررسی قرار نگرفته اند:
-
UDF + assignment SELECT
-
PIVOT + ROW_NUMBER
-
Multiple Subquery
-
Multiple Apply
-
Recursive UDF
-
Non Set-based: Cursor
مراجع:
Simple-Talk
Plamen Ratchev (SQL Server MVP)