Hi Bhavesh23,
Check the below query.
SQL
CREATE TABLE #Employees
(
InwardDate DATETIME,
EmployeeName VARCHAR(50)
)
INSERT INTO #Employees VALUES('2022-07-01','Ram Das')
INSERT INTO #Employees VALUES('2022-07-02','Arjun Das')
INSERT INTO #Employees VALUES('2022-07-01','Ram Das')
INSERT INTO #Employees VALUES('2022-07-03','Dharm Das')
INSERT INTO #Employees VALUES('2022-07-02','Ram Das')
INSERT INTO #Employees VALUES('2022-07-03','kali Das')
INSERT INTO #Employees VALUES('2022-07-05','Lila Das')
INSERT INTO #Employees VALUES('2022-07-01','Bali Das')
INSERT INTO #Employees VALUES('2022-07-05','Ram Das')
INSERT INTO #Employees VALUES('2022-07-01','Babu Das')
INSERT INTO #Employees VALUES('2022-07-06','Ram Das')
INSERT INTO #Employees VALUES('2022-07-06','Karim Das')
INSERT INTO #Employees VALUES('2022-07-06','Karim Das')
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX), @colsFormated AS NVARCHAR(MAX)
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(DAY(InwardDate))
FROM #Employees
FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
set @query =';WITH CTE AS(SELECT EmployeeName,DAY(InwardDate) InwardDate FROM #Employees)
SELECT * INTO #Temp FROM CTE
SELECT ''Count'' AS ''Date'',' + @cols +' FROM #Temp
PIVOT
(
COUNT(EmployeeName)
FOR InwardDate IN (' + @cols +')
) p
DROP TABLE #Temp'
EXECUTE sp_executesql @query
DROP TABLE #Employees
Output
| Date |
1 |
2 |
3 |
5 |
6 |
| Count |
4 |
2 |
2 |
2 |
3 |
