Hi indradeo,
You have to use pivot query to get the result.
CREATE TABLE #emp_mast_lead_lag (
[id] INT IDENTITY (1, 1) NOT NULL,
[EMPNO] NCHAR (6) NOT NULL,
[EmpName] NVARCHAR (50) NULL,
[department] NVARCHAR (50) NULL,
[designation] NVARCHAR (50) NULL,
[mobile] FLOAT (53) NULL,
[email_id] NCHAR (50) NULL,
[Password] VARCHAR (50) NULL
)
INSERT INTO #emp_mast_lead_lag VALUES(7221,'DP Charwarty','IT','AGM',NULL,NULL,7221)
INSERT INTO #emp_mast_lead_lag VALUES(5778,'Davendar Goel','FES','AGM',NULL,NULL,5778)
INSERT INTO #emp_mast_lead_lag VALUES(6453,'Surjeet Bahadur Singh','Electrical Erection','AGM',NULL,NULL,6453)
CREATE TABLE #lead_lag (
[Id] INT IDENTITY (1, 1) NOT NULL,
[EMPNO] NCHAR (6) NULL,
[NAME] VARCHAR (50) NULL,
[DEPARTMENT] VARCHAR (50) NULL,
[Year] NCHAR (10) NOT NULL,
[Month] NCHAR (10) NOT NULL,
[DATE] VARCHAR (50) NOT NULL,
[INDICATER_TYPE] VARCHAR (50) NULL,
[INDICATER_Desc] VARCHAR (50) NOT NULL,
[COMPLIED ] VARCHAR (50) NULL,
[REMARK ] VARCHAR (500) NULL,
[flag] BIT DEFAULT ('N') NULL
)
INSERT INTO #lead_lag VALUES(7221,'DP Charwarty','IT','2018-2019','10','10-11-2020 12:26','Leg','Deviation','Yes','test','TRUE')
INSERT INTO #lead_lag VALUES(5778,'Davendar Goel','FES','2018-2019','10','10-11-2020 12:26','Lead','Deviation','Yes','test','TRUE')
INSERT INTO #lead_lag VALUES(6453,'Surjeet Bahadur Singh','Electrical Erection','2018-2019','10','10-11-2020 12:26','Leg','Pep/Talk','No','test','FALSE')
SELECT * FROM
(
SELECT COUNT(b.INDICATER_TYPE)as totalRow,a.DEPARTMENT,b.INDICATER_TYPE
FROM #emp_mast_lead_lag a
INNER JOIN #lead_lag b ON a.EMPNO = b.EMPNO
GROUP BY a.DEPARTMENT,b.INDICATER_TYPE
) t
PIVOT (SUM(totalRow) FOR INDICATER_TYPE IN ([Leg],[Lead])
)P
DROP TABLE #emp_mast_lead_lag
DROP TABLE #lead_lag
For dynamic column name refer below link.
