Data TutorialsSQL Tips

Using the Unpivot Step to make a Tabular Table out of a Crosstab Table

Posted by Tim Miller

If your table is in a crosstab format and you are looking to get it into a flat tabular format, Chartio has a Data Pipeline step that can help you accomplish this task.

After querying the columns you want in the Chart Builder, our crosstab style table appears.

We have in this example the Student ID# of a number of students in our school and the test results from 3 different aptitude tests 1 A, 1 B, and 1 C.

table of student ids and test scores

What we are after in this case is to count the number of times each score appears in each aptitude test.

  1. After we get the crosstab style table, we can use 1 Data Pipeline step to unpivot the table into a tabular format.

unpivot step changes table into long format

  1. Then we can just add a column that replicates the “Score” column, and the purpose for that is to use a group step to count all returns of each score. Then we need to hide the Student ID# column.

duplicating the score column and hiding student ids

  1. Here is where we will use the group Data Pipeline step to get the count of each potential score for each of the three tests. Selecting GROUP as the grouping options for the first two columns, we can select COUNT for the score replica column.

obtain a frequency count of scores by grouping by test and original test score

  1. Now we can use a re-order step and a pivot step to get the count of each score, under a column header that represents each test.

setting the original scores as the first column and re-pivoting gives a frequency count by test

  1. We can now visualize this table as a stacked bar chart to show how each test’s scores were distributed.

stacked bar chart of number of scores, divided by test

This workaround will help you easily flatten a crosstab table instead of having to write a monotonous query with numerous UNIONs. This will give you the ability to visualize these crosstab tables quickly and get to the insights your decision makers are looking to reach.