Creating crosstab() pivot table in PostgreSQL 9.0

All we need is an easy explanation of the problem, so here it is.

I have a rather complicated issue for myself which I hope someone can help me with.

I want to use PostgreSQL to enable me to create pivot tables from a geometry-less table.

To keep it simple I will just show the table structure i want to use for the pivot and then use the methods given to create others, I hope 🙂

The fields I want to use are postcode_nospace_, and ageband.

ageband will be the columns across the top with postcode_nospace_ being the rows with a count of each postcode being the data.

There are 2132 records and of which some have more than one postcode, hence the count.

CREATE TABLE adult_social_care.activities_in_localities_asc
(
  ogc_fid integer NOT NULL,
  sort numeric(5,0),
  ageband character(12),
  postcode_nospace_ character(8),
  wkb_geometry geometry,
  CONSTRAINT activities_in_localities_asc_pkey PRIMARY KEY (ogc_fid)
);

UPDATE:

Here is the outcome I want to achieve as shown in an excel pivot table with the same data.

postcode 18_24  25_34   35_44   45_54   55_64   65_74   Total Count
----------------------------------------------------------------------------
BB115DE     1           2                   2      3       8
FY38LZ                              1       1              2

From looking around I have compiled the following SQL query. It groups by postcode and creates the field names required. However the fields are blank. I would ideally also want a total_count column at the end of the table.

SELECT * FROM crosstab(
    'SELECT postcode_nospace_, ageband, count(ageband) as total_count
     FROM adult_social_care.activities_in_localities_asc
     GROUP BY postcode_nospace_, ageband
     ORDER BY postcode_nospace_'

     ,$$VALUES ('18-24'::text), ('25-34'), ('35-44'), ('45-54'), ('55-64'), ('65-74'), ('75-84'), ('85-94'), ('95 AND OVER')$$)
AS ct("postcode" text, "18-24" numeric, "25-34" numeric,"35-44" numeric, "45-54" numeric, "55-64" numeric, "65-74" numeric, "75-84" numeric, "85-94" numeric, "95 AND OVER" numeric);

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Your table definition says

...
ageband character(12),
...

which means the values in there look like '18-24 ' instead of '18-24'. This way, the items from the VALUES list do not match the values in the table, therefore you get an empty table as result.

If you change the column type to a bit more senseful text (which trims the values, too, as Erwin points it out):

ALTER TABLE activities_in_localities_asc ALTER COLUMN ageband TYPE text;

you will get your desired results.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply