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...