So I have a very complicated crosstab query for my client, who manages student aid for various colleges. The query is dynamic, so it's built as a long string in the stored procedure, then prepared, executed and deallocated.
I've been able to extract the final query, which is ~2000 characters long.
select concat(s.LastName, ', ', s.FirstName, ' ', ifnull(s.MiddleInit, '')) as StudentName, p.SSN,
case @fullSSN when 0 then concat('xxxx-xx-', right(p.ssn, 4)) else concat(left(p.ssn, 3), '-',
substr(p.ssn, 4, 2), '-', right(p.ssn, 4)) end as formattedSSN,
sp.AwardYear, c.CampusName, es.EnrollmentStatus, sp.LoanPeriodFrom, sp.LoanPeriodTo, pg.ProgramName,
concat(s.Address1, ', ', s.City, ', ', s.State, ' ', s.Zip) as Address, s.Email, s.DOB,
sp.StartDate, sp.EndDate as LDA, sp.VerifyType,
sum(case when FedPgmName = 'DL Subsidized' then Amount end) as 'DL Subsidized',
sum(case when FedPgmName = 'DL Unsubsidized' then Amount end) as 'DL Unsubsidized',
sum(case when FedPgmName = 'Pell' then Amount end) as 'Pell',
sum(case when FedPgmName = 'DL PLUS' then Amount end) as 'DL PLUS',
sum(case when FedPgmName = 'FSEOG' then Amount end) as 'FSEOG',
sum(case when FedPgmName = 'FWS' then Amount end) as 'FWS'
from Payments as p inner join Student as s on p.SchoolID = s.SchoolID and p.SSN = s.SSN inner join
(select SchoolID, SSN, Max(StudentProfileID) as MaxProfileID from StudentProfile group by SchoolID,
SSN) as max on p.SchoolID = max.SchoolID and p.ssn = max.ssn inner join StudentProfile as sp on
max.SchoolID = sp.SchoolID and max.SSN = sp.SSN and max.MaxProfileID = sp.StudentProfileID inner
join Program as pg on sp.SchoolID = pg.SchoolID and sp.ProgramID = pg.ProgramID inner join as fp
on p.FedPgmID = fp.FedPgmID inner join Campuses as c on sp.SchoolID = c.SchoolID and sp.CampusID =
c.CampusID inner join EnrollmentStatus as es on sp.EnrollmentStatusID = es.EnrollmentStatusID where
p.SchoolID = 'cbd' and CkDate between '2018-01-01' and '2018-12-31' and sp.ProgramID in(161, 24, 25,
168, 165, 166, 14, 159, 160, 13, 150, 151, 17, 23, 10, 15, 2, 16, 3, 26, 9, 21, 22, 1, 11, 12, 19,
20, 4, 18, 8, 5, 6, 7) and p.FedPgmID in(1, 8, 9, 10, 5, 6, 23) and sp.EnrollmentStatusID in(1, 10,
2, 3, 4, 5, 6, 7, 8, 9) and ifnull(CkNo, 0) > 0 group by StudentName, SSN, formattedSSN, AwardYear,
CampusName, EnrollmentStatus, LoanPeriodFrom, LoanPeriodTo, ProgramName
It's not important to know exactly what's happening here, except that my problem involves the crosstab code above where there are several cases of different federal program names. If the underlying data has all the different programs mentioned, then everything works fine. If any of the mentioned cases are missing from the data (such as no rows with "FWS"), then that column does not show in the results, and my code has problems, because it's expecting all the columns to be returned.
So I'm trying to figure out how to make sure all of the columns are returned. If I add the following to the beginning of the generated SQL code, it works ok:
select '' as StudentName, '' as SSN, '' as formattedSSN, '' as AwardYear, '' as CampusName, '' as EnrollmentStatus,
'' as LoanPeriodFrom,
'' as LoanPeriodTo, '' as ProgramName, '' as Address, '' as Email, '' as DOB, '' as StartDate, '' as LDA, '' as VerifyType,
select 0 as 'DL Subsidized', 0 as 'DL Unsubsidized', 0 as 'Pell', 0 as 'DL PLUS', 0 as 'FSEOG', 0 as 'FWS'
union
Making the query a union query with the above code works. All columns are returned, but then I have an "empty" row at the top which I have to code for.
Bear in mind the query is very complex, and has to be built using a lot of concats, plus I have to do a group_concat just to get the names of the possible federal programs. This creates the case statements for me, which get injected into the main SQL statements.
So is there any way to force all columns to appear other than doing that union query thingy? Hope I made the issue clear.
Thanks...
EDIT: Turns out adding the union query doesn't work after all. I was running off data that already included records for all federal programs. If I run that union query with a data set that doesn't include some federal programs, I get an error about the 2 queries not matching column count. So I'm still having problem of returning all possible columns in the crosstab part.
Couldn't figure this out, so I solved the problem a different way. Since a crosstab query won't supply any columns in the crosstab that are not valid values in the underlying data, I created a table containing all the possible columns and then truncate and fill the table from the crosstab query. Finally, select from that table. All possible columns now appear.