This series is dedicated to all the 3-in-1 Analyst / DBA / SQL Developers working in school districts. Much respect.
Results from the State of Texas Assessments of Academic Readiness (STAAR) for 5th and 8th Grade Reading arrived last week. Our team receives a fixed-width file containing data from multiple tests combined into one record per student. For our analysis, we often need to transform the one record into multiple rows: one row per student per test.
It’s possible to do this using UNION operators. However, this often results in repetitive and lengthy code.
Instead, I tried the UNPIVOT operator. Since I needed to report results for each scholar, each test, each reporting category (or content grouping), and also include the performance levels, I actually used UNPIVOT multiple times. These result sets must be joined — which I did rather messily. I also used the DENSE_RANK function to sort the reporting categories from lowest to highest score.
Here’s a snippet of the TSQL:
SELECT AdministrationYear ,GradeLevel ,... ,StudentLastName ,... ,ReportingCategoryField ,convert(int,ReportingCategoryRawScore) as RawScore ,...
FROM (SELECT year(f.administrationdate) as AdministrationYear ,convert(int,f.GradeLevelTested) as GradeLevel ,... ,f.lastname as StudentLastName ,... ,f.ReadingReportingCategoryScores_1 ,f.ReadingReportingCategoryScores_2 ,f.ReadingReportingCategoryScores_3 ,... FROM ff.staar f) p UNPIVOT (ReportingCategoryRawScore for ReportingCategoryField IN (ReadingReportingCategoryScores_1 ,ReadingReportingCategoryScores_2 ,ReadingReportingCategoryScores_3 ,... ) ) AS S
I’ve attached the Transact-SQL query that will do the data transformation using the UNPIVOT operator and also posted it in our GitHub repository. The query assumes data is stored in the FF.STAAR table and also joins to a DAT.ReportingCategory table, which contains the STAAR Reporting Categories for each test. I’ve included the data for this table in the attachment.