Following on from my previous question:
I managed to create a pivot table for
ageband using the
crosstab() function. I can use this to either create a view or table of the base geometry-less table.
However, this still isn't much use as I need to link it to the
gazetteers_and_addresses.unit_postcode table in order to assign geometries for further analysis.
I will attach the table structure for both tables and the original code that worked to create my crosstab.
CREATE OR REPLACE VIEW adult_social_care.vw_ageband AS ( 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));
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) );
CREATE TABLE gazetteers_and_addresses.unit_postcode ( oogc_fid serial NOT NULL, pc_area character(10), postcode_nospaces text, wkb_geometry geometry );
If possible too, assign another field at the end which states a sum of all the fields to give a
If this can be done then I can create dynamic views on different factors using one geometry-less table and