Please, have a look at example:
DECLARE @pvttable1 TABLE(ID VARCHAR(30), [NAME] VARCHAR(30), DAY1 VARCHAR(30), DAY2 VARCHAR(30), DAY3 VARCHAR(30), DAY4 VARCHAR(30), DAY5 VARCHAR(30), DAY6 VARCHAR(30), DAY7 VARCHAR(30))
INSERT INTO @pvttable1 (ID, [NAME], DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7)
VALUES('empid001', 'jo', '2', 'G', 'G', 'SL', 'G', 'G', 'G'),
('empid022', 'oj', 'G', 'SL', 'G', 'SL', 'G', 'G', 'G'),
('empid333', 'yo', '2', 'G', 'SL', 'SL', 'G', 'G', 'G'),
('empid999', 'oy', 'SL', 'G', 'G', 'SL', 'G', 'G', 'SL')
DECLARE @pvttable2 TABLE (ID VARCHAR(30), [Name] VARCHAR(30), [Code] VARCHAR(30), CountOfCode INT)
INSERT INTO @pvttable2 (ID, [Name], [Code], CountOfCode)
SELECT ID, [Name], [Code], COUNT([Code]) CountOfCode
FROM (
SELECT ID, [NAME], [Day], [Code]
FROM(
SELECT ID, [NAME], DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7
FROM @pvttable1
) AS pvt
UNPIVOT ([Code] FOR [Day] IN (DAY1, DAY2, DAY3, DAY4, DAY5, DAY6, DAY7)
) AS unpvt
) AS T
GROUP BY ID, [Name], [Code]
ORDER BY ID, [Name], [Code]
SELECT pvt1.ID, pvt1.[Name], pvt1.DAY1, pvt1.DAY2, pvt1.DAY3, pvt1.DAY4, pvt1.DAY5, pvt1.DAY6, pvt1.DAY7, COALESCE(pvt2.[2],0) AS CountOf2, COALESCE(pvt2.G,0) AS CountOfG, COALESCE(pvt2.SL,0) AS CountOfSL
FROM @pvttable1 AS pvt1 INNER JOIN (
SELECT ID, [Name], [2], [G], [SL]
FROM (
SELECT *
FROM @pvttable2
) As DT
PIVOT(SUM(CountOfCode) FOR [Code] IN ([2], [G], [SL])) AS PVT
) AS pvt2 ON pvt1.ID = pvt2.ID
Result:
ID Name DAY1 DAY2 DAY3 DAY4 DAY5 DAY6 DAY7 Co..f2 Co..fG Co..fSL
empid001 jo 2 G G SL G G G 1 5 1
empid022 oj G SL G SL G G G 0 5 2
empid333 yo 2 G SL SL G G G 1 4 2
empid999 oy SL G G SL G G SL 0 4 3