4

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);
4

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.

  • Yes this worked perfectly. If say I changed the column type to character varying would this of worked without the trim? I want to now add a column at the end called 'total_count' which would contain a sum of the columns to create a new column. I have tried many variations to try and get this to work to no avail. More importantly than this though, I want to create this table spatially by using an 'INNER JOIN' or something like that to enable me to view the data more intelligently. I know that I could create the table and then take do a separate SQL query to do this, but strive for perfection. – daniel franklin Sep 8 '15 at 14:00
  • A cast from char(n) to text or varchar does not conserve blanks, so the UPDATE (with trim()) is not necessary. – Erwin Brandstetter Sep 8 '15 at 14:03
  • and in relation to the other part, can this be done? – daniel franklin Sep 8 '15 at 14:06
  • 3
    @danielfranklin: Please start a new question for the new question. Comments are not the place. You can always link to this one for context. – Erwin Brandstetter Sep 8 '15 at 14:08
  • 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.