I'm using PostgreSQL 14.1 with PostGIS 3.1 and I've got two tables
malla, that contains a UTM grid of 10x10 km, and
CLC18, that contains the habitat polygons of the Corine Land Cover project. Now I want to know the area of each habitat that is included in each cell of the grid, and I have built this code:
select m.utmcode utm, round(Sum(st_area(st_intersection(m.geom,c.geom)))/10000) area, c.code_18 code from public.malla m left join public.clc18 c on st_intersects(m.geom,c.geom) group by utm, code ;
but it returns several rows for each UTM cell, one for each habitat type. I'd rather have in columns all the Corine types/codes (there are 44 of them) and only one row for each UTM cell. What query should I use to get that?