Hi,
As i mention i have tried to make you output check this from your table i have added a new columname as relation_key
check the query belo
CREATE TABLE [dbo].[Temp]( [code] [char](4) NULL, [name] [varchar](50) null, [AttendanceDate] [datetime] NULL, [Status] [char](2)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [reason] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,relation_key int ) ON [PRIMARY]
insert into Temp values('B001', 'Bbc', '2014-03-05 00:00:00.000', 'P','',1)
insert into Temp values('A001', 'Abc', '2014-03-05 00:00:00.000', 'P','',1)
pivot query.
DECLARE @Start datetime ='2014-03-01'
,@End datetime='2014-03-31',
@DList varchar(2000),
@Sql varchar(max)
IF OBJECT_ID('tempdb..#TEMP_Everyday') IS NOT NULL
DROP TABLE #TEMP_Everyday
SELECT DATEADD(dd,a.number,@Start) AS Date ,CONVERT(VARCHAR(2),DATEADD(dd,a.number,@Start),6) as d ,1 as relation_key
into #TEMP_Everyday
FROM
master..spt_values a
WHERE
type='p' AND
DATEADD(dd,a.number,@Start)<=@End
IF OBJECT_ID('tempdb..#TEMP_final') IS NOT NULL
DROP TABLE #TEMP_final
select distinct a.Code ,a.name,
case when a.AttendanceDate=b.date then a.status else '' END as Status
,a.reason,b.date,b.d
into #TEMP_final
from
#TEMP_Everyday b left outer join
temp a
ON b.relation_key=a.relation_key
group by a.Code , a.AttendanceDate,a.status,a.name,a.reason,b.date,b.d
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(d)
from #TEMP_Everyday
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Code,name,' + @cols + '
from #TEMP_final
pivot
(
min(status)
for status in (' + @cols + ')
) p '
execute(@query)
Note here the data is now not matched you can filter and match the data as you needed