Transpose generate series date postgresql

Question

Questions about transpose are asked many times before, but I cannot find any good answer when using generate_series and dates, because the columns may vary.

WITH range AS
(SELECT to_char(generate_series('2015-01-01','2015-01-05', interval '1 day'),'YYYY-MM-DD'))
SELECT * FROM range;

The normal output from generate series is:

2015-12-01
2015-12-02
2015-12-03
... and so on

http://sqlfiddle.com/#!15/9eecb7db59d16c80417c72d1e1f4fbf1/5478

But I want it to be columns instead

2015-12-01  2015-12-02  2015-12-03  ...and so on

It seems that crosstab maybe should do the trick, but I only get errors:

select * from crosstab('(SELECT to_char(generate_series('2015-01-01','2015-01-05', interval '1 day'),'YYYY-MM-DD'))')
as ct (dynamic columns?)

How do I get crosstab to work with generate_series(date-date) and different intervals dynamically?

TIA

What about this SELECT string_agg(col,',') from( select to_char(generate_series('2015-01-01','2015-01-05', interval '1 day'),'YYYY-MM-DD') col )t — Vivek S.
Yes, it is on the right track! But is there a way to get this into columns also? — sibert
How will you give column name for each items?, there will be 10 or even 100 columns can be produce — Vivek S.
or this select arcol[1],arcol[2],arcol[3],arcol[4],arcol[5] from ( SELECT array_agg(col) arcol from( select to_char(generate_series('2015-01-01','2015-01-05', interval '1 day'),'YYYY-MM-DD') col )t )u — Vivek S.
@wingedpanther "How will you give column name for each items?, there will be 10 or even 100 columns can be produce". Either just numbers or letter+number? — sibert

Abhishek Ginani Abhishek Ginani · Accepted Answer · 2016-01-06T10:30:04

Taking Reference from link PostgreSQL query with generated columns.

you can generate columns dynamically:

create or replace function sp_test()
returns void as
$$

declare cases character varying;
declare sql_statement text;
begin
    drop table if exists temp_series;
    create temporary  table temp_series as
    SELECT to_char(generate_series('2015-01-01','2015-01-02', interval '1 day'),'YYYY-MM-DD') as series;

    select string_agg(concat('max(case when t1.series=','''',series,'''',' then t1.series else ''0000-00-00'' end) as ','"', series,'"'),',') into cases from temp_series;

    drop table if exists temp_data;
    sql_statement=concat('create temporary table temp_data as select ',cases ,' 
    from temp_series t1');

    raise notice '%',sql_statement;
    execute sql_statement;
end;
$$
language 'plpgsql';

Call function in following way to get output:

select sp_test(); select * from temp_data;

Updated Function which takes two date paramaeters:

create or replace function sp_test(start_date timestamp without time zone,end_date timestamp without time zone)
returns void as
$$

declare cases character varying;
declare sql_statement text;
begin
    drop table if exists temp_series;
    create temporary  table temp_series as
    SELECT to_char(generate_series(start_date,end_date, interval '1 day'),'YYYY-MM-DD') as series;

    select string_agg(concat('max(case when t1.series=','''',series,'''',' then t1.series else ''0000-00-00'' end) as ','"', series,'"'),',') into cases from temp_series;

    drop table if exists temp_data;
    sql_statement=concat('create temporary table temp_data as select ',cases ,' 
    from temp_series t1');

    raise notice '%',sql_statement;
    execute sql_statement;
end;
$$
language 'plpgsql';

Function call:

select sp_test('2015-01-01','2015-01-10'); select * from temp_data;

Transpose generate series date postgresql

1 Answers