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