0

I have a attendance table like this:

Student_id   attendance_date    status
-----------  ----------------   ---------
       1     1/8/2014          Present
       2     1/8/2015          Absent
       1     2/8/2014          Present
       2     2/8/2015          Present
       1     3/8/2014          Present
       2     3/8/2015          Present

Expected output is this:

  Student_ id   1/8/2014      2/8/2014     3/8/2014
 -------------  ---------     ---------   ----------
         1     present         present      present
         2     absent          present      present

Please help me to get a expected output.....

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Dileep Kumar
  • 510
  • 1
  • 4
  • 19

2 Answers2

0

You need to create a procedure and use Dynamic Pivot for this like below. 

DECLARE @dates NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)

--Get all the dates to pivot
SELECT @dates = ISNULL(@dates  + ',','') 
       + QUOTENAME(attendance_date)
FROM (SELECT DISTINCT attendance_date FROM attendance) AS attendance 

--Construct the dynamic pivot query

SET @query = 
  N'SELECT status, ' + @dates + '
    FROM attendance
    PIVOT(MAX(status) 
          FOR dates IN (' + @dates + ')) AS pivotTable'

-- excecute the dynamic query

EXEC sp_executesql @query
Kiran Hegde
  • 3,651
  • 1
  • 16
  • 14
  • You need format the date strings as well, particularly `FOR dates IN...` here you are comparing a date or datetime value to a string. And include a date range filter in both the query that sets the @dates and the @query values. – Paul Maxwell Aug 09 '14 at 13:41
0

may be this works 

SELECT   t.[user_id], t.[attendance_date],t.[status] 
     FROM    Attendance t 
 PIVOT   ( MIN(status) 
      FOR attendance_date IN ([1/8/2014],[2/8/2014],[3/8/2014]) ) pvt;
mohan111
  • 8,633
  • 4
  • 28
  • 55
  • i am getting following error:----Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. – Dileep Kumar Aug 11 '14 at 08:17