pivot table - date data rows to columns

Question

i have a SQL table that stores attendance ( punch in and punch out) of employees, i am currently working to get the total working hour of every day stored in the table(the total days is about 22 to 30 days maximum).. the data stored is like below:

ID  date        time        Status
1   01/08/2015  08:00 AM    IN
1   01/08/2015  01:00 PM    OUT
1   01/08/2015  02:30 PM    IN
1   01/08/2015  07:30 PM    OUT
1   02/08/2015  11:00 AM    IN
1   02/08/2015  06:00 PM    OUT
1   02/08/2015  09:30 PM    IN
1   03/08/2015  02:30 AM    OUT
1   03/08/2015  08:00 AM    IN
1   03/08/2015  06:00 PM    OUT
1   04/08/2015  08:00 AM    IN
1   04/08/2015  06:00 PM    OUT

now i want the data to be like below:

ID  01/08/2015  02/08/2015  03/08/2015  04/08/2015  total
1       10          12          10          10      42
2       9           10          10          11      40

i have read a lot of article about the pivot table but all of them use a specific number of columns inside it, so how to solve it dynamically?

Answer 1

DECLARE @cols AS VARCHAR(MAX)
DECLARE @query  AS VARCHAR(MAX)

select  @cols = STUFF((SELECT ',' + QUOTENAME( cast(CONVERT( VARCHAR(20), Edate,103 ) as varchar(10)))   
                    from   
                    (  
                     select a1.Edate from (
                        SELECT DISTINCT   Edate 
                        from InOut  
                      )a1

                    ) t  

            FOR XML PATH(''), TYPE  
            ).value('.', 'VARCHAR(MAX)')   
        ,1,1,'')  

    EXEC( ' SELECT Id,' + @cols + '    
            from   
            (  
              select Id,
                 Edate ,  
                status
              from  
              (  
                select convert(varchar(20), Edate,103)Edate, Id,status
                from InOut
              ) src  
            ) x  
            pivot   
            (  
                count(status)  
                for Edate in (' + @cols + ')  
            ) p where 1=1 ')