Have a look at example:
CREATE TABLE #tblTicketDetail
(
TicketID INT,
IssuerName NVARCHAR(30),
TicketDescription NVARCHAR(30)
)
CREATE TABLE #tblEngineer
(
EngineerID INT IDENTITY(1,1),
[Name] NVARCHAR(30)
)
CREATE TABLE #tblTicket_Engineer
(
TicketID INT,
EngineerID INT
)
INSERT INTO #tblTicketDetail (TicketID, IssuerName, TicketDescription)
VALUES(1, 'Saqib', 'qwerty keyboard')
INSERT INTO #tblEngineer ([Name])
VALUES('Imran Khan'), ('Mubeen Khan'), ('Faraz Ahmed')
INSERT INTO #tblTicket_Engineer (TicketID, EngineerID)
VALUES(1,1),(1,2),(1,3)
SELECT TicketID, IssuerName, TicketDescription, [1], [2], [3]
FROM (
SELECT TD.TicketID, TD.IssuerName, TD.TicketDescription, ROW_NUMBER() OVER(ORDER BY TE.EngineerID) AS EngineerNo, EN.[Name]
FROM #tblTicketDetail AS TD
INNER JOIN #tblTicket_Engineer AS TE ON TD.TicketID = TE.TicketID
INNER JOIN #tblEngineer AS EN ON TE.EngineerID = EN.EngineerID
WHERE (TE.TicketID = 1)
) AS DT
PIVOT(MAX([Name]) FOR [EngineerNo] IN([1], [2], [3])) AS PT
DROP TABLE #tblTicketDetail
DROP TABLE #tblEngineer
DROP TABLE #tblTicket_Engineer
Result:
Tic..ID Issu... TicketDesc... 1 2 3
1 Saqib qwerty keyboard Imran Khan Mubeen Khan Faraz Ahmed