نمایش رکوردهای متناظر یا فرزند مربوط به والد به صورت رشته متصل

گاهی اوقات لازم است که رکوردهای جدول فرزند مرتبط با یک جدول والد به صورت یک رشته واحد نمایش داده شود. مثلا تمامی درسهای اخذ شده یک دانش آموز به همراه نمره آنها در یک خط یا یک فیلد نمایش داده شود. لطفا به لیست زیر دقت فرمائید تا بهتر متوجه شوید:

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