Using Pivot in SQL

Empid	EmpName	EmpDay	empStatus
1	Bikash	1	P
2	Bikash	2	P
3	Bikash	3	P
4	Bikash	4	A
5	Bikash	5	A
6	Bikash	6	P
7	Bikash	8	P
8	Rama Rao	1	P
9	Rama Rao	2	P
10	Rama Rao	6	P
11	Rama Rao	3	P
12	Rama Rao	7	P

SELECT EMPNAME,[1],[2],[3],[4],[5],[6],[7],[8],[9] FROM (SELECT EMPNAME,EMPDAY,EMPSTATUS FROM EMPTEST) X PIVOT (MAX(EMPSTATUS) FOR EMPDAY IN([1],[2],[3],[4],[5],[6],[7],[8],[9])) Y

EMPNAME	1	2	3	4	5	6	7	8	9
Bikash	P	P	P	A	A	P	NULL	P	NULL
Rama Rao	P	P	P	NULL	NULL	P	P	P	NULL

DECLARE @cols AS NVARCHAR(MAX)
DECLARE@query AS NVARCHAR(MAX)
 
SET @cols = (SELECT DISTINCT ',['+ CAST(EMPDAY AS VARCHAR(10)) +']' FROM EMPTEST FOR XML PATH(''))
SELECT @cols= SUBSTRING(@COLS,2,LEN(@cols))
  SET @query = 'SELECT EMPNAME,'+@cols+' FROM
(SELECT EMPNAME,EMPDAY,EMPSTATUS FROM EMPTEST) AS X
PIVOT (MAX(EMPSTATUS) FOR EMPDAY IN('+@cols+')) P'
EXEC (@query)