SQL Concating results from a result set

Question

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.

Are you using SQL Server, and, if so, [have you read this SO question](https://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) before posting? — Tim Biegeleisen, Aug 10 '18 at 05:46
sorry for the inconvenience @TimBiegeleisen . i have read that , but i dont know whats the problem in my question. please let me know. — Krunal Shah, Aug 10 '18 at 05:48
You have an answer below, and the query you want will probably look like this. — Tim Biegeleisen, Aug 10 '18 at 05:48

Timshel · Accepted Answer · 2018-08-10T06:34:08.070

0

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.

See this db<>fiddle

edited Aug 10 '18 at 06:34

answered Aug 10 '18 at 05:47

Timshel

1,653
12
9

its giving me error - The multi-part identifier "MstImplementerStaffTraningDetail.ObjectID" could not be bound. – Krunal Shah Aug 10 '18 at 05:48
Looking again I realise that the ObjectID column also belongs to the TraineeStaff table; so there are also multiple of these coming in for each training detail record. Looking at your example, it's difficult to understand what you are trying to achieve by your query. If you can give more details of what you actually want from the query, I might be able to help further. – Timshel Aug 10 '18 at 06:22
I've edited the answer so that the query provides the exact output you're wanting from example you've provided; but without knowing the answer to exactly what you're wanting to achieve, I can't guarantee that it is doing what you want. – Timshel Aug 10 '18 at 06:35

SQL Concating results from a result set

1 Answers1