MS SQL Dynamic Pivot from Stored Procedure result

Question

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

• Peter: Can you provide script with sampledata.

Answer 1

I think I managed to get the result I wanted. I used python to extract the data from the stored procedure, pivot the tabled and exported it to a csv file.

from sqlalchemy import create_engine
import pandas as pd
import pyodbc
import os
server = 'serverName'
database = 'database'
conn_str = 'Driver={SQL Server Native Client 11.0};Server=' + server + ';Database=' + database + ';Trusted_Connection=yes;'
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
params = ('PV/AM/B5/2023', ) 
cursor.execute("{CALL GetStudentAttendanceMonthlyReport (?)}", params)
rows = cursor.fetchall()
df = pd.DataFrame.from_records(rows, columns=[column[0] for column in cursor.description])
unique_months = df['Month'].unique()
pivot_df = pd.pivot_table(df, values='TOTAL', index='StudentID', columns='Month', aggfunc='first')
desired_column_order = sorted(pivot_df.columns, key=lambda col: list(unique_months).index(col))
pivot_df = pivot_df[desired_column_order]
order_df = pd.DataFrame(desired_column_order, columns=['columns'])
order_df['index'] = range(1, len(order_df) + 1)
csv_order_filename = 'column_order.csv'
order_df.to_csv(csv_order_filename, index=False)
csv_filename = 'attendance_report.csv'
pivot_df.to_csv(csv_filename, index=True) 

The thing is now I'm stuck at creating the report I want. That's another problem.