I have 3 tables as Student, Batches and StudentAttendanceRegistry. I managed to join these tables and get a desired result as following,
StudentID Name Year Month TotalPresentDays PV/AM/B5/001 Asan 2023 August 12 PV/AM/B5/001 Asan 2023 September 2 PV/AM/B5/001 Asan 2023 October NULL PV/AM/B5/001 Asan 2023 November NULL PV/AM/B5/001 Asan 2023 December NULL PV/AM/B5/001 Asan 2024 January NULL PV/AM/B5/001 Asan 2024 February NULL PV/AM/B5/001 Asan 2024 March NULL PV/AM/B5/001 Asan 2024 April NULL PV/AM/B5/001 Asan 2024 May NULL PV/AM/B5/001 Asan 2024 June NULL PV/AM/B5/001 Asan 2024 July NULL PV/AM/B5/001 Asan 2024 August NULL
Here is my stored procedure for the above result https://pastebin.com/ANn4kSdQ
You can see the Year and Month are not in order since these are dynamically generated using the BatchStartDate and BatchEndDate values from Batches table. I want to get these Year and Month rows into Columns and fill them with their respective TotalAbsentDays accordingly. And get a result like below,
StudentID Aug 2023 Sep 2023 Oct 2023 ... Jan 2024 Feb 2024 ... PV/AM/B5/001 12 2 NULL NULL NULL PV/AM/B5/002 6 5 NULL NULL NULL ...
I pasted the above mentioned table's create queries here. https://pastebin.com/4Qymt22R
I'm very new to SQL altogether. Just today I discovered the existence of Pivot in SQL Server. I just want to create a report of student attendance in Crystal Reports. If you think I could do that without using pivot, any instructions or advice are much appreciated. Thank you...