This is a bit interesting problem. I have created a sample solution for you that you can extend as per your need. The actual issue is 15000 courses that you can't export as columns. Practically speaking in a given date range (semester) we are sure that very limited number of courses will be used (should be in 100s) that students gets enrolled. Taking this assumption we first need to know the exact number of courses that we are talking about. We can get this information via executing a query only getting courses. Once we get this course codes, we will put this in Pivot query and get our desired result. Below is the sample solution for your review.
We will generate pre-req data
CREATE TABLE SOME_TABLE
(
Student_ID varchar(10),
Dept_Code varchar(10),
Course_Code varchar(10),
Status varchar(20)
)
INSERT INTO SOME_TABLE(Student_ID, Dept_Code, Course_Code,Status)
SELECT '1001', 'KP1', 'PHC', 'Completed'
UNION ALL
SELECT '1001', 'KP1', 'AMP', 'Enroll'
UNION ALL
SELECT '1001', 'KP1', 'CEF', 'Not Enrolled'
UNION ALL
SELECT '1001', 'KP1', 'CIP', 'Started'
UNION ALL
SELECT '1001', 'KP1', 'GEN', 'Completed'
UNION ALL
SELECT '1001', 'KP1', 'MER', 'Not Enrolled'
UNION ALL
SELECT '1001', 'EC2', 'COT', 'Completed'
UNION ALL
SELECT '1001', 'EC2', 'MER', 'Enroll'
UNION ALL
SELECT '1001', 'EC2', 'AMP', 'Not Enrolled'
Now we will get all eligible course codes
declare @dynamicCourseCode varchar(1000), @SQL varchar(2000)
select @dynamicCourseCode =
stuff((
select distinct ',' + Course_Code
from SOME_TABLE
for xml path('')
),1,1,'')
from SOME_TABLE
Finally, we will put these comma-separated course code values in a dynamic sql query and execute the query
SELECT @SQL = 'SELECT *
FROM
(
SELECT
Student_ID,
Dept_Code,
Course_Code,
Status
FROM SOME_TABLE
) AS P
PIVOT
(
MAX(Status) FOR Course_Code IN (' + @dynamicCourseCode + ')
) AS pvt
'
EXEC (@SQL)
This will give you your desired result. There could be a query size limitation that you may face. In that case, you can move the entire logic to Stored Procedure.
Hopefuly this should solve your problem.