0

I am Trying to get attendance report from existing database

here is the sample view of database 

UserID   Day         Attendance 
1        2014-12-01  1
1        2014-12-02  0
2        2014-12-01  1
2        2014-12-02  0
2        2014-12-03  1

Here is the Output I need to get 

UserID    2014-12-01    2014-12-02   2014-12-03
1         1             0            null
2         1             0            1

i have tried using PIVOT but i can't manage to get multiple rows as columns

here is the running Example of sample database : SQL Fiddle

i really appreciate any help or suggestion

[EDIT]

i need to run this query in MS ACcess database

nicole ino
  • 33
  • 1
  • 9
  • 1
    Tag your question with the database you are using. Edit your question with the query that you attempted. – Gordon Linoff Dec 26 '14 at 12:59
  • i am using MS access database, i can't get any working solutions rather than error queries,i wrote a query on Fiddle Example – nicole ino Dec 26 '14 at 13:19

2 Answers2

1

Simply you can do by using SQL Pivot. Use Temp table for getting days in month and generate Columns then these columns use in Pivot and Convert your Attendance Date in Day so that columns can match. you can use date as well without converting day. but little bit change will required there you can get also generate dynamic columns mean only date that you are having then not use Temp Table,In STUFF select statement use you current table and get date column. but hope it will help you.

DECLARE @cols AS NVARCHAR(MAX),
@query  AS NVARCHAR(MAX)
DECLARE @TempAttendanceMonth table(C_Date int)
DECLARE @cnt INT = 1;
WHILE @cnt <= 30
  BEGIN
   insert into @TempAttendanceMonth (C_Date) values (@cnt)
   SET @cnt = @cnt + 1;
  END;
select @cols = STUFF((SELECT ',' + QUOTENAME(C_Date)
from @TempAttendanceMonth
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
set @query 
  = 'SELECT StudentId,' + @cols + ' 
from 
     (
       select StudentId, day(CurrentDate)CurrentDate, IsPresent
from StudentAttendance
     ) src
     pivot 
     (
        sum(IsPresent)
        for CurrentDate in (' + @cols + ')
     )piv '
execute(@query)
Galaxy Starts
  • 221
  • 2
  • 5
0

You could try using temp tables using pivot where the columns are unique values in the 'Day' column of your original table . Once you have your temp table you can write queries to populate it with the data from your original table.

Xion
  • 318
  • 1
  • 5
  • 19
  • you mean create temp table for dynamic part right ? do you have any example ? Thanks.. – nicole ino Dec 27 '14 at 05:13
  • Check these links --------- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a642017b-0ee0-4f04-8022-27fc49696493/pivot-table-saving-it-after-creation-insert-into-or-select-into?forum=transactsql --------------------- http://stackoverflow.com/q/778079/3394346 --------------------- https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7d67adc5-6f91-4639-98d6-513db4322096/insert-into-temp-table-from-unknown-number-and-name-of-columns-from-dynmic-pivot-query?forum=transactsql – Xion Dec 27 '14 at 20:51