Get startedGet started for free

A basic pivot

You have the following table of Pole Vault gold medalist countries by gender in 2008 and 2012.

| Gender | Year | Country |
|--------|------|---------|
| Men    | 2008 | AUS     |
| Men    | 2012 | FRA     |
| Women  | 2008 | RUS     |
| Women  | 2012 | USA     |

Pivot it by Year to get the following reshaped, cleaner table.

| Gender | 2008 | 2012 |
|--------|------|------|
| Men    | AUS  | FRA  |
| Women  | RUS  | USA  |

This exercise is part of the course

PostgreSQL Summary Stats and Window Functions

View Course

Exercise instructions

  • Create the correct extension.
  • Fill in the column names of the pivoted table.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Create the correct extension to enable CROSSTAB
CREATE EXTENSION IF NOT EXISTS ___;

SELECT * FROM CROSSTAB($$
  SELECT
    Gender, Year, Country
  FROM Summer_Medals
  WHERE
    Year IN (2008, 2012)
    AND Medal = 'Gold'
    AND Event = 'Pole Vault'
  ORDER By Gender ASC, Year ASC;
-- Fill in the correct column names for the pivoted table
$$) AS ct (___ VARCHAR,
           ___ VARCHAR,
           ___ VARCHAR)

ORDER BY Gender ASC;
Edit and Run Code