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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81

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')
GO
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 
VALUES      (2,N'Mehrdad')
GO
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 
VALUES      (3,N'BITA')
GO
INSERT INTO [dbo].[tblStudents] ([StID] ,[StNameSname]) 
VALUES      (4,N'AZAM')
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (1,1,N'Riazi',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (2,1,N'Fizik',18)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (3,1,N'Shimi',16)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (4,2,N'Riazi',18)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (5,2,N'Fizik',19.5)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (6,2,N'Shimi',14.5)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (7,2,N'Shimi',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (8,3,N'Riazi',14)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (9,3,N'Fizik',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (10,4,N'Shimi',20)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (11,4,N'Honar',19.4)
GO
INSERT INTO [dbo].[tblStudentsMarks] ([StMarkID] ,[StID],[CurseName],[MarkOfCurse])
VALUES     (12,4,N'Khaiiati',20)
GO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

USE DbTest
GO
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
GO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

USE DbTest
GO
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 
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
GO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

USE DbTest
GO
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
GO