TSQL Tuesday: Transform STAAR data using UNPIVOT

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.

— Lori

1 – STAARbyReportingCategory

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s