Creating crosstab() pivot table in PostgreSQL 9.0

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, "

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.

StackExchange Database Administrators Q#114428, answer score: 5