| PIDM | TEST | SCORE |
| 1 | S1 | 560 |
| 1 | S2 | 650 |
| 1 | S1 | 600 |
Unfortunately, this is typically not the way one would prefer to report this information. More often, we'd like to see each person's test scores in a single line with multiple columns for each test:
| PIDM | S1 | S2 |
| 1 | 600 | 650 |
The second example is much more accessible, allowing the removal of duplicated students, and provides the reader with a more meaningful way to view institutional data (in this case, test scores).
So, How Can this Be Done?!
Well, SQL provides all the tools necessary to make this happen. The two most important tools from our SQL tool belt that will be used for this particular issue will be the DECODE (or CASE) and MAX functions. Let us first take a look at the raw data:
Query 1 - Basic Test Data
Results 1 - Basic Test Data
As you can see, test information is stored in a manner that is difficult to report, but it won't be for long! The next step is to switch the view of the information from row to columner form. To do this, we will check the test code to create each column:
Query 2 - Perform the Pivot
Results 2 - Performing the Pivot
The DECODE function, whose syntax is DECODE(column_name,'X','A','Y','B','Z','C','D'), operates as follows: it checks the column_name value to see if it is equal to 'X'. If it is equal to 'X' then it replaces itself with the value 'A'. If it is not equal to 'X' then it checks to see if it is equal to 'Y'. If it is equal to 'Y' then it replaces itself with the value of 'B' and so on until we get to 'D'. As you probably noticed, 'D' is the odd man out; it has no corresponding return value. This is referred to as the "Default". If column_name doesn't equal 'X','Y', OR 'Z', then the decode statement will return 'D' by "Default". As you can see in the above example, this only got us half way there; we still have more than one row per individual. Also, with test codes, it's possible to have more than one score with the same code. This problem is easily solved with the MAX() aggregate function. If we surround each column (other than the PIDM) with the MAX function, it will only return the highest test score for each test, leaving one row per individual. This is exactly what we want! See the following example:
Query 3 - Finalize the Pivot
Output 3 - Final Pivot
As we expected, this final output is much easier to read than the first (row style) output. However, this technique does have it's draw backs and limitations: - You have to know in advance what you're going to key off to perform the pivot - in this case, we used the test codes.
- You have to make a column for every row you want to view as a column. In this case, we only chose 7 tests, but what if you wanted to see every test a student has taken?
Posts
