CODEWITHSUNDEEP
sqlsql-serverpivot
user1645200
user1645200

Reputation: 145

SQL Server query to show summary list for particular months or between dates

I have an attendance table which contains student's attendance date and attendance status.

Is there any way to display a summary something like this:

Student 20-09-2012 21-09-2012 22-09-2012 23-09-2012
  xyz       P          A          P           P
  abc       P          P          P           P

Through a SQL Server query?

I have tried with the PIVOT but it is not working because of aggregate function.

Upvotes: 1

Views: 1003

Answers (3)

user6146701
user6146701

Reputation: 1

DECLARE @DynamicPivotQuery AS NVARCHAR(max) 
DECLARE @ColumnName AS NVARCHAR(max) 

SELECT a.grno, 
       a.a_date [Date], 
       a.status, 
       s.first_name, 
       s.last_name 
INTO   #pivotdata 
FROM   attendance_master AS a, 
       student_master AS s 
WHERE  sid = " & dd_sub.selecteditem.value & " 
       AND s.grno = a.grno 
       AND a.a_date >= '" & txtdate1.Text & "' 
       AND a.a_date <= '" & txtdate2.Text & "' 
GROUP  BY a.grno, 
          a.status, 
          a.a_date, 
          s.last_name, 
          s.first_name 

SELECT @ColumnName = Isnull(@ColumnName + ',', '') 
                     + Quotename([date]) 
FROM   (SELECT DISTINCT [date] 
        FROM   #pivotdata) AS Dates 

SET @DynamicPivotQuery = 'SELECT grno,first_name,last_name,' 
                         + @ColumnName 
                         + 'FROM #PivotData PIVOT(sum(status) FOR [Date] IN (' 
                         + @ColumnName + ')) AS PVTTable1' 

EXEC Sp_executesql 
  @DynamicPivotQuery

enter image description here

Upvotes: 0

tsionyx
tsionyx

Reputation: 1669

Your problem reminds me of the similar one that I had to resolve. With dynamic SQL your problem solves as follows

-- create list of all dates 
DECLARE @dates varchar(1000) = ''
SELECT @dates = @dates + ',[' + CAST(t.[date] as varchar)+']'
FROM ( SELECT DISTINCT [date] FROM Attendance) t

-- remove first comma
IF LEN(@dates)>1 SET @dates = RIGHT(@dates,LEN(@dates)-1)


-- create query with UNPIVOT and PIVOT
 exec('SELECT 
        StudentName,'+
        @dates+
   'FROM(
        SELECT  
            [date],
            [stat],
            StudentName 
        FROM 
            (SELECT 
                    [date],
                    [status],
                    StudentName 
                FROM 
                    Attendance
            ) AS t
        UNPIVOT 
            ( stat FOR s in ([status])) unpvt
        ) dummy
    PIVOT 
        (
            MAX(stat) FOR [date] in ('+@dates+')
        ) pvt
    ORDER BY StudentName;');

That is SqlFiddle for your request

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

I have tried with PIVOT but it not working because of aggregate function.

You can use MAX as an aggregate function like so:

SELECT t.StudentName,
  MAX(CASE WHEN t.Date = '20120920' THEN t.Status END) AS '20-09-2012',
  MAX(CASE WHEN t.Date = '20120921' THEN t.Status END) AS '21-09-2012',
  MAX(CASE WHEN t.Date = '20120922' THEN t.Status END) AS '22-09-2012',
  MAX(CASE WHEN t.Date = '20120923' THEN t.Status END) AS '23-09-2012'
FROM Attendence t
GROUP BY t.StudentName

Upvotes: 1

Related Questions