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

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.