Clash Royale CLAN TAG#URR8PPP

SQL Concating results from a result set

I am not getting the required result from what i have tried.

This is my mstTraineeStaff Table -

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]


Records in TraineeSTaff Table -

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')


This is my detail table for traineeStaff -

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]


Records in Detail Table are as follow -

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)


My Required Result should be like this -

GroupCode | StaffName | ObjectID
----------+-----------------------+------------
19 | dhara,krunal | 1
19 | krunal | 2
19 | dhara,krunal,harshida | 3


But i am getting it like this

GroupCode | StaffName | ObjectID
----------+-------------+------------
19 | dhara | 1
19 | dhara | 3
19 | krunal | 1
19 | krunal | 2
19 | krunal | 3
19 | harshida | 3


So far , i have tried like this to get above result -

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


Any help would be appreciated. Thanks.

1 Answer
1

This might be what you want:

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


The main query just builds a list each valid ObjectID for each GroupCode, and then the subquery builds a comma-separated list of StaffName for each GroupCode / ObjectID pair.

ObjectID

GroupCode

StaffName

GroupCode

ObjectID

See this db<>fiddle

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.