, MAX(coord) coord, MAX(s.num) surface_num, MAX(s.surface_id) surface_id, c.curve_id, MAX(CASE WHEN c.num=1 THEN c.xml_pid ELSE '' END) pid1, MAX(CASE WHEN c.num=2 THEN c.xml_pid ELSE '' END) pid2, MAX(CASE WHEN c.num=1 THEN p.x ELSE null END)::float p1x, MAX(CASE WHEN c.num=1 THEN p.y ELSE null END)::float p1y, MAX(CASE WHEN c.num=2 THEN p.x ELSE null END)::float p2x, MAX(CASE WHEN c.num=2 THEN p.y ELSE null END)::float p2y FROM moj_surface s LEFT OUTER JOIN moj_basics b ON (b.ver=s.ver AND b.filename=s.filename) LEFT OUTER JOIN moj_curves_data c ON (s.ver=c.ver AND s.filename=c.filename AND s.curve_id=c.curve_id ) LEFT OUTER JOIN moj_points p ON (b.ver=c.ver AND b.filename=p.filename AND p.xml_pid=c.xml_pid) WHERE b.filename LIKE '12222-%.zip' AND coord LIKE '公共座標%' GROUP BY b.ver, b.filename, c.curve_id ORDER BY ver, filename, surface_id, surface_num ), t2 AS( SELECT filename, surface_id, ST_MakeLine(ST_MakePoint(p1y,p1x)) g FROM t1 GROUP BY filename, surface_id ) SELECT filename, surface_id, ST_Transform(ST_SetSRID(ST_MakePolygon(ST_AddPoint(g,ST_StartPoint(g))), 6668+9), 6668) FROM t2; Query: