How to create new columns from a query result

Question

This is a result of a query:

The landuse column contains multiple attributes of polygons.

Now I want to create n new columns for the same table that have exactly the names from that query result strings (allotments, cemetery, commercial .... ).

I would do it manually, but I don't know all attributes that exist. Therefore, I need a programmatical solution.

I could not find a fitting solution in other posts.

CREATE TABLE wu_stations_karlsruhe_landcover_features AS (
WITH city_stations AS (
    SELECT      city,      centerid,      loc
      --,ST_Buffer(loc :: GEOGRAPHY, 1000) AS stat_surr -- Take 1km radius to find close land coverage polygons
    FROM wunderground_stations_within_large_cities
    WHERE city = 'Karlsruhe'
),
    stations_landuse_landcover AS
  (
      SELECT        city,        centerid, landuse, "natural", AVG(cs1.loc::geography <-> gp1.way::geography) AS dist, SUM(ST_Area(way::geography))
      FROM ger_polygon_landcover AS gp1, city_stations AS cs1
      WHERE ST_DWithin(cs1.loc::geography, gp1.way::geography, 1000)
      GROUP BY city, centerid, landuse, "natural"
      ORDER BY city, centerid, landuse, "natural"
  )
SELECT *
FROM stations_landuse_landcover
);

Do you have the actual SQL string that pulled this data or just this result? — Isaiah3015, Jul 24 '17 at 15:27
therefore, you need one new table having all the different column names from the attribute landuse? — Radim Bača, Jul 24 '17 at 15:29
What you're looking for is a dynamic pivot.. Take a look at https://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns — Caius Jard, Jul 24 '17 at 15:52
Please include text as text whenever possible, not images/links. Ie not this image. We can't cut & paste or search it. — philipxy, Jul 26 '17 at 01:51
I added the query to the question. Actually I even need to combine natural and landuse, if both exist.. so I have many null value - combinations — byebb, Jul 26 '17 at 09:49

score 0 · Answer 1 · answered Jul 24 '17 at 15:35

Probably the easiest way is to write a SQL query to generate SQL that you can then run against your database. This isn't uncommon when working with large data-sets but you need to be careful you are generating SQL that won't cause you issues when you run it!

I've outlined a simple example below:

CREATE TABLE Example (
  ID    int NOT NULL,
  Name  text NOT NULL,
  PRIMARY KEY (ID)
);

INSERT INTO Example VALUES (1,'Grass'), (2,'Allotments'), (3,'Meadow');

SELECT concat('ALTER TABLE Example ADD COLUMN ' , NAME, ' text NULL;')
FROM Example

The output of this is SQL that you should then check carefully before running against your DB.

How to create new columns from a query result

1 Answers1