Join 34,000+ subscribers and receive articles from our blog about software quality, testing, QA and security.

SQL for a pivot table for management



I have been trying to get some data for a pivot table for management - in a non-hosted install.

We need to find all tests run between dates, by project, case title, who ran the case, and the result (passed, failed, blocked) of the test.

One of the issues is that the person who created the run (we exclusively use Test Runs) is not the same as who was assigned to the test. In couple of our groups - QA is more of a test manager. QA sets up the test run and then the person who runs the actual individual test assigns the test case to themselves.

So only way I can see who was assigned the test (essentially who ran the individual test) is going off of the Test table. The test table has a LOT of records per run_id. One of the test runs has 85 individual tests, assigned between 2 people but the test table 137 records for the run_id and of course the case_id for each record in the table is different.

Additionally the only place to check dates for what was run is from the Test_Activity table which compounds the issue.

Is there any way to get the info we need?




Hello Brian,

Thanks for your posting. The details you are looking for are stored in the test_changes table which contains a record for each test result (+ assignments, comments, etc.). Each record contains a creation date (created_on, as UNIX timestamp), a reference to the user who added it (user_id), a possible status (status_id) and more. References to the test and test run are available via the test_id and run_id.

Please note that the test_changes table can contain even more records than the tests table because there can be multiple test results/changes per test (please keep this in mind when querying the database). An aggregated summary of the test activity can be found in the test_activities table but this wouldn’t contain details about the users who added the results so I’m not sure if this would work for you.

I also want to emphasize again (also for other readers) that we only support direct database access for read-only queries.