Select Contents of array or column of another table as row headers

Question

I am trying to create a table: say a metric for places in time series

place 1990 1991 1992 1993
NM     23   34   21   43
..     ..   ..   ..   ..

The data source table has each year as one row. I need to dynamically create the headers 1990,1991... by looking at the source table and get the metrics there

This is in PostgreSQL

I have created an array of years and trying to "select" years by looping to generate a table. Please see code

DO
$do$
DECLARE
    time varchar;
BEGIN
    FOREACH time IN ARRAY  (select ARRAY_agg(a) from (select year from headers) as a)
    LOOP 
       select time;
    END LOOP;
END;
$do$

This code will not run but I would like to see the result equivalent to without having to type the dates as string (they are in another table)

select 'jan_1990', 'jan_1991', 'jan_1992' ..

I would say just `UNION` two queries to get that first row there — Miroslav Glamuzina, Oct 03 '19 at 20:29

score 0 · Answer 1 · answered Oct 03 '19 at 21:57

0

You may want to look at another post that shows how to transpose:

PostgreSQL convert columns to rows? Transpose?

answered Oct 03 '19 at 21:57

richyen

8,114
4
13
28

score 0 · Accepted Answer · answered Oct 04 '19 at 17:15

I found a solution to the problem

DO
$do$
DECLARE
    time varchar;
BEGIN
    FOREACH time IN ARRAY(//data returned from select)
    LOOP 
        EXECUTE format('ALTER TABLE table_name ADD COLUMN %s datatype;', btrim(time,'()')); 
        -- brtim was necessary as time is returned as (jan-2019).. with the brackets       
    END LOOP;
END;
$do$;

Select Contents of array or column of another table as row headers

2 Answers2