Display multiple Tables data in single row in SQL Server

Hi indradeo,

Please refer the query below.

SQL

CREATE TABLE MGPS_main
(
	gpnum        int,
	gatepasstype VARCHAR(50),
	deptctrlnum	 VARCHAR(50),
	recname		 VARCHAR(50),
	datecreation DATETIME,
	deptcode	 VARCHAR(50),
	preparedby	 VARCHAR(50),
	authorizedby VARCHAR(50),
	authdate	 DATETIME
)
INSERT INTO MGPS_main VALUES(15,'R','Anand','TEST','2/28/2023 11:39:13 AM','0520','101768','NULL','00:00.0')

CREATE TABLE MGPS_sub
(
	gpnum           INT,
	sno				INT,
	vehiclenum		VARCHAR(50),
	materialtype	VARCHAR(50),
	materialdesc	VARCHAR(50),
	quantitynum		VARCHAR(50),
	quantityunit	VARCHAR(50),
	materialissueto	VARCHAR(50),
	datereturn		DATETIME,
	exittime        VARCHAR(50),
	statusflag      VARCHAR(50)
)
INSERT INTO MGPS_sub VALUES(15,1,'UP70EKXXX','XXXXXX','2','NUMBER','XXXXXX','NULL','2/28/2023 12:00:00 AM','NULL','NULL')

CREATE TABLE emp_mast
(
	emp_num				INT,
	emp_name			VARCHAR(50),
	ntpc_joining_date	DATETIME,
	fstpp_joining_date	DATETIME,
	dept_code			VARCHAR(50),
	emp_sex_code		VARCHAR(50),
	pay_status			VARCHAR(50),
	emp_status			VARCHAR(50),
	cadre				VARCHAR(50),
	designation_code    VARCHAR(50)
)
INSERT INTO emp_mast VALUES(101768,'UPMA SINGH','00:00.0','00:00.0','520','1','1','Y','E','E5')

CREATE TABLE EA_DeptCode_Mas 
(
	Dept_id	  INT,
	deptName  VARCHAR(50),
	shortname VARCHAR(50)
)
INSERT INTO EA_DeptCode_Mas VALUES(520,'INFORMATION TECHNOLOGY','INFORMATION TECHNOLOGY')

SELECT a.gpnum, b.sno, b.materialdesc,b.quantitynum, b.quantityunit, b.datereturn, b.exittime, a.datecreation, c.deptname as DeptName,b.statusflag
FROM MGPS_main a
INNER JOIN MGPS_sub b ON a.gpnum = b.gpnum
INNER JOIN EA_DeptCode_Mas c ON a.deptcode = c.dept_id
INNER JOIN emp_mast d ON c.dept_id = d.dept_code
WHERE datereturn BETWEEN '2023-02-28 00:00:00.000' AND '2023-02-28 11:39:13.890' AND gatepasstype = 'R' AND emp_num ='101768'
ORDER BY b.gpnum DESC, B.SNO

Output