Posted an hour ago i have made sql query for pivot Attendance ,my output is 5-1-2019 6-1-2019 7-1-2019 8-1-2019 P A P A But I want this Output 5 6 7 8 P A P A
ALTER Procedure [dbo].[PIvot_Attendance] As Begin SELECT DISTINCT (date) as date INTO #Datessss FROM EmployeeAtt ORDER BY date DECLARE @cols NVARCHAR(4000) SELECT @cols = COALESCE(@cols + ',[' + CONVERT(varchar, (date), 106) + ']','[' + CONVERT(varchar, date, 106) + ']') FROM #Datessss ORDER BY date -- Building the query with dynamic dates DECLARE @qry NVARCHAR(4000) SET @qry = 'SELECT * FROM (SELECT empcode, Day ,(date) FROM EmployeeAtt)emp PIVOT (MAX(Day) FOR date IN (' + @cols + ')) AS stat' -- Executing the query EXEC(@qry) End
DECLARE @qry NVARCHAR(4000) = N'SELECT * FROM (SElECT empcode, DATEPART(DAY, [date]) AS [DayOfMonth], [date] FROM EmployeeeAtt) AS emp PIVOT (MAX(DATEPART(DAY, [date]) FOR [date] IN (' + cols + ')) AS stat'
01/02/03
DatePart
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)