Using Pivot Table to Show Student Attendance

Question

I m using SQL 'Pivot' to show Student Attendance on monthly basis but facing Error Invalid column name 'DayValue'. Invalid column name 'Stu_Status'.

M using following SQL Query

Select 
       StudentID, Stu_Status, DAY([AttandanceDate]) as [DayValue] 
  From tbl_Students_Attandance as studAttd
 PIVOT (Max([Stu_Status])
   FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],
       [10],  [11], [12], [13], [14],[15], [16], [17], [18], [19],
       [20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30])
       ) AS Piviottable;

using below table Design

SQL Table Design Image

To achieve below result Required result Image

You are in the right direction, give some more effort, you can achieve your desired output in the same way. — Shushil Bohara, Jul 17 '18 at 09:52
Please update your question adding tables in text format instead of images — Andrea, Jul 17 '18 at 09:59
have a look at :https://stackoverflow.com/q/51306478/7124761 I used it for last seven days — Prashant Pimpale, Jul 17 '18 at 10:09
Can you explain how do you want your day's like As per current month or from any table — Prashant Pimpale, Jul 17 '18 at 10:11

score 0 · Answer 1 · answered Jul 17 '18 at 10:01

0

You can use conditional aggregation instead :

select StudentID, max(case when day(AttandanceDate) = 1 then Stu_Status end) as [1],
       . . . 
from  tbl_Students_Attandance as studAttd
group by StudentID;

However, i suspect this will also require to mention year() to match current year & month attendance.

answered Jul 17 '18 at 10:01

Yogesh Sharma

49,870
5
26
52

can u give me fiddle or some working example @Yogesh Sharma ? – Zain Ali Jul 17 '18 at 10:08

score 0 · Accepted Answer · answered Jul 17 '18 at 10:17

TRY THIS: There are few issues in your query so if it works then compare both queries and try to understand what you just missed

SELECT StudentID, [1], [2], [3], [4],[5], [6], [7], [8], [9],
       [10],  [11], [12], [13], [14],[15], [16], [17], [18], [19],
       [20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30], [31]
FROM(
    Select 
       StudentID, Stu_Status, DAY([AttandanceDate]) as [DayValue] 
  From tbl_Students_Attandance
) AS studAttd
PIVOT
(
    MAX(Stu_Status)
    FOR [DayValue] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],
       [10],  [11], [12], [13], [14],[15], [16], [17], [18], [19],
       [20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30], [31])
) AS pvt

Using Pivot Table to Show Student Attendance

2 Answers2