SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE [dbo].[MstTraineeStaff](
    [Code] [int] IDENTITY(1,1) NOT NULL,
    [GroupCode] [int] NOT NULL,
    [StaffName] [nvarchar](60) NOT NULL,
    [Role] [nvarchar](60) NOT NULL,
    CONSTRAINT [PK_MstTraineeStaff] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = 
    OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[MstImplementerStaffTraningDetail](
    [Code] [int] IDENTITY(1,1) NOT NULL,
    [HeaderCode] [int] NOT NULL,
    [SequenceNo] [int] NOT NULL,
    [ImplementerCode] [int] NOT NULL,
    [ObjectID] [int] NOT NULL,
    [TraningDateTime] [datetime] NOT NULL,
    [IsTrained] [tinyint] NOT NULL,
CONSTRAINT [PK_MstImplementerStaffTraningDetail] PRIMARY KEY CLUSTERED 
(
    [Code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
SET IDENTITY_INSERT dbo.MstTraineeStaff ON

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (1, 11, N'Parth', N'manager')

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (2, 11, N'krunal', N'developer')

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (3, 11, N'dhara', N'developer')

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (4, 11, N'Harshida', N'Developer')

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (5, 19, N'dhara', N'develper')

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (6, 19, N'krunal', N'developer')

INSERT [dbo].[MstTraineeStaff] ([Code], [GroupCode], [StaffName], [Role]) VALUES (7, 19, N'harshida', N'developer')

SET IDENTITY_INSERT dbo.MstTraineeStaff OFF

SET IDENTITY_INSERT dbo.MstImplementerStaffTraningDetail ON

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (1, 1, 1, 1, 1, CAST(0x0000A937009935AF AS DateTime), 1)

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (2, 2, 1, 1, 1, CAST(0x0000A93700A1945A AS DateTime), 1)

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (3, 5, 1, 1, 1, CAST(0x0000A93700D63594 AS DateTime), 1)INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (4, 6, 1, 1, 1, CAST(0x0000A93700D63596 AS DateTime), 1)

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (5, 6, 1, 1, 2, CAST(0x0000A93700D63FC7 AS DateTime), 0)

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (6, 5, 1, 1, 3, CAST(0x0000A93700D64786 AS DateTime), 1)

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (7, 6, 1, 1, 3, CAST(0x0000A93700D64787 AS DateTime), 1)

INSERT [dbo].[MstImplementerStaffTraningDetail] ([Code], [HeaderCode], [SequenceNo], [ImplementerCode], [ObjectID], [TraningDateTime], [IsTrained]) VALUES (8, 7, 1, 1, 3, CAST(0x0000A93700D64789 AS DateTime), 1)

SET IDENTITY_INSERT dbo.MstImplementerStaffTraningDetail OFF
15 rows affected

select MstTraineeStaff.GroupCode,MstTraineeStaff.StaffName,MstImplementerStaffTraningDetail.ObjectID
from MstTraineeStaff
    left join MstImplementerStaffTraningDetail on MstImplementerStaffTraningDetail.HeaderCode = MstTraineeStaff.Code
    where GroupCode = 19


GroupCode
StaffName
ObjectID


19
dhara
1

19
dhara
3

19
krunal
1

19
krunal
2

19
krunal
3

19
harshida
3


SELECT
    ts.GroupCode,
    (SELECT (STUFF(
                (SELECT     ',' + trainee.StaffName 
                FROM        MstTraineeStaff trainee LEFT JOIN
                        MstImplementerStaffTraningDetail detail ON detail.HeaderCode = trainee.Code
                WHERE       trainee.GroupCode = ts.GroupCode
                  AND       detail.ObjectID = istd.ObjectID
                FOR XML PATH('')),
        1, 2, ''))) AS StaffNameList,
    istd.ObjectID
FROM MstTraineeStaff ts LEFT JOIN
    MstImplementerStaffTraningDetail istd ON istd.HeaderCode = ts.Code
WHERE ts.GroupCode = 19
GROUP BY ts.GroupCode, istd.ObjectID


GroupCode
StaffNameList
ObjectID


19
hara,krunal
1

19
runal
2

19
hara,krunal,harshida
3