نمایش همه فرزندان والد در یک خط در SQL Server

گاهی اوقات لازم است که رکوردهای جدول فرزند مرتبط با یک جدول والد به صورت یک رشته واحد نمایش داده شود. مثلا تمامی درسهای اخذ شده یک دانش آموز به همراه نمره آنها در یک خط یا یک فیلد نمایش داده شود. لطفا به لیست زیر دقت فرمائید تا بهتر متوجه شوید:
StID StNameSname
----------- --------------
1 ALI
2 Mehrdad
3 BITA
4 AZAM
و جدول فرزند به شکل زیر است
StMarkID StID MarkOfCurse CurseName
----------- ----------- ---------------------- ------------
1 1 20 Riazi
2 1 18 Fizik
3 1 16 Shimi
4 2 18 Riazi
5 2 19.5 Fizik
6 2 14.5 Shimi
7 2 20 Shimi
8 3 14 Riazi
9 3 20 Fizik
10 4 20 Shimi
11 4 19.4 Honar
12 4 20 Khaiiati
که در دو جدول فوق فیلد STID فیلد مشترک بین دو جدول می باشد. خروجی که مد نظر است به شکل زیر میباشد:
StID StNameSname Children
----------- ----------------- -------------------------------------------------
1 ALI Riazi : 20,Fizik : 18,Shimi : 16
2 Mehrdad Riazi : 18,Fizik : 19.5,Shimi : 14.5,Shimi : 20
3 BITA Riazi : 14,Fizik : 20
4 AZAM Shimi : 20,Honar : 19.4,Khaiiati : 20
همانگونه که مشاهده می نمایید تمامی نمرات هر دانش آموز در مقابل نام آنها نوشته شده است. برای نوشتن کوئری که نتایجه فوق خروجی آن باشد چند روش را مورد بررسی قرار می دهیم.
در ایتدا دیتابیس و جدولها را ایجاد می نماییم
USE [master]
GO
-- SQL 2016 بررسی وجود پایگاه داده و کد حذف آن برای نسخهای قبل از
IF DB_ID('DbTest')>0
BEGIN
ALTER DATABASE [DbTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [DbTest]
END
GO
-- و بالاتر SQL 2016 بررسی جهت وجود دیتابیس و حذف آن در
-- DROP DATABASE IF EXISTS sampleDB
-- GO
-- ایجاد دیتابیس
create Database DbTest
go
use [DbTest]
go
-- ایجاد جدول
CREATE TABLE tblStudents
(
StID INT PRIMARY KEY
,StNameSname NVARCHAR(100)
)
go
CREATE TABLE tblStudentsMarks
(
StMarkID INT PRIMARY KEY
,StID INT
,CurseName NVARCHAR(100)
,MarkOfCurse float
)
go
-- درج چند رکورد در جدول
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname])
VALUES
(1,N'ALI'),(2,N'Mehrdad'),(3,N'BITA'),(4,N'AZAM')
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES
(1,1,N'Riazi',20),(2,1,N'Fizik',18),(3,1,N'Shimi',16),
(4,2,N'Riazi',18),(5,2,N'Fizik',19.5),(6,2,N'Shimi',14.5),(7,2,N'Shimi',20),
(8,3,N'Riazi',14),(9,3,N'Fizik',20),
(10,4,N'Shimi',20),(11,4,N'Honar',19.4),(12,4,N'Khaiiati',20)
GO
روش اول: استفاده از دستور FOR XML PATH و STUFF
SELECT DISTINCT ST2.StID, ST2.StNameSname,
STUFF((SELECT ', ' + GD1.CurseName + N' : ' + convert(nvarchar(20) , GD1.MarkOfCurse) AS [text()]
FROM (select s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID) GD1
WHERE GD1.StID = ST2.StID
ORDER BY GD1.StID FOR XML PATH('')), 1 ,1, '') [StudentsMarks]
FROM (select s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID) AS ST2
روش دوم: استفاده از دستور FOR XML PATH و SUBSTRING
SELECT DISTINCT ST2.StID, ST2.StNameSname,
SUBSTRING ((SELECT ', ' + GD1.CurseName + N' : ' + convert(nvarchar(20) , GD1.MarkOfCurse) AS [text()]
FROM (select s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID) GD1
WHERE GD1.StID = ST2.StID --AND GD1.PFactorDetailID = ST2.PFactorDetailID
ORDER BY GD1.StID FOR XML PATH('')), 2, 1000) [StudentsMarks]
FROM (select s.StID , s.StNameSname, m.StMarkID , m.CurseName, m.MarkOfCurse
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID) AS ST2
روش سوم: استفاده از دستور FOR XML PATH و STUFF و WITH CTE
with StTemp (StudID , StName , StCurseName , StMark)
as
(
select s.StID , s.StNameSname, m.CurseName, m.MarkOfCurse
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID
)
SELECT DISTINCT StudID, StName,
REPLACE(
STUFF(
(SELECT
',' + t2.StCurseName + N' : ' + convert(nvarchar(20) , StMark)
FROM StTemp t2
WHERE StTemp.StudID=t2.StudID
ORDER BY t2.StudID
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,2, ''
),
',,', ',') AS ChildValues
FROM StTemp
روش چهارم: استفاده از متد STRING_AGG ورژن اس کیو ال باید 2017 یا بالاتر باشد.
select s.StID , s.StNameSname, STRING_AGG(m.CurseName + N' : ' + convert(nvarchar(20) , m.MarkOfCurse) , ',') as Children
from tblStudents s
inner join tblStudentsMarks m on s.StID = m.StID
group by s.StID , s.StNameSname
خروجی
StID
|
StNameSname
|
StudentsMarks
|
1
|
ALI
|
Riazi : 20, Fizik : 18, Shimi : 16
|
2
|
Mehrdad
|
Riazi : 18, Fizik : 19.5, Shimi : 14.5, Shimi : 20
|
3
|
BITA
|
Riazi : 14, Fizik : 20
|
4
|
AZAM
|
Shimi : 20, Honar : 19.4, Khaiiati : 20
|
پرفورمنس:
استفاده از دستور String_agg دارای بهترین پرفورمنس و استفاده از With در مثال فوق دارای بد ترین پرفورمنس می باشد.