Unpivoting Columns to Rows

Abstract

Ideally, you’d hope always to work with data that’s nicely normalized in your relational database, the way they teach in computer science classes. In reality it’s quite often not as ideal. One quite common pattern is to have some data with a bunch of columns, where you’d really like those data as rows with, for example, key-value pairs, where the key would be derived from the original column name and the value then would be the value from that column. The act of turning data in rows into columns is called pivoting (which is the topic of the next chapter), so as this is the reverse operation, it is called unpivoting. Unpivoting is shown with examples based on tables that contain data from an external source – that’s of course not always the case, but it is not uncommon.