PostgreSQL Column-to-Row Transposition

I recently had a need to generate a geolocation history of user activity. The result set needed to be a linear history of users' activities, with each row consisting of a user identifier, activity type, location, and timestamp. Unfortunately our database schema stored the location and timestamp of three different types of activities across 3 separate pairs of columns in the same table. To accomplish the required output, I needed to transpose the columns into rows. To accomplish this, I was able to make use of PostgreSQL's array constructor syntax and unnest array function.

From a table with the following columns:

• user_id
• activity1_location
• activity1_timestamp
• activity2_location
• activity3_timestamp
• activity3_location
• activity3_timestamp

I issued the following query:

SELECT user_id, 
unnest(ARRAY['activity1', 
             'activity2',
             'activity3']) as "activity type",

unnest(ARRAY[activity1_location, 
             activity2_location, 
             activity3_location]) as "location",

unnest(ARRAY[activity1_timestamp, 
             activity2_timestamp, 
             activity3_timestamp]) as "timestamp";

The unnest function generates multiple rows, one row per element of the specified array. This produces a result such as:

user_id | activity_type | location    | timestamp

--------+---------------+-------------+--------------------

      1 | activity1     | address1    | 2012-03-13 00:00:00

      1 | activity2     | address2    | 2012-03-13 00:00:01

      1 | activity3     | address3    | 2012-03-13 00:00:02

      1 | activity1     | address4    | 2012-03-13 00:01:00

      1 | activity2     | address5    | 2012-03-13 00:01:01

      1 | activity3     | address6    | 2012-03-13 00:01:02

      2 | activity1     | address7    | 2012-03-14 00:00:00

      2 | activity2     | address8    | 2012-03-14 00:00:01

      2 | activity3     | address9    | 2012-03-14 00:00:02

...

I learned about these PostgrSQL array functions from this highly recommended slide presentation, "Postgres: The Bits You Haven't Found".