how to preserve column names on dynamic pivot

Question

Sales data contains dynamic product names which can contian any characters.

Dynamic pivot table is created based on sample from Crosstab with a large or undefined number of categories

translate() is used to remove bad characters.

In result pivot table column names are corrupted: missing characters and spaces are removed. How to return data with same column names as in source data ? I tried to use

  quote_ident(productname) as tootjakood,

instead of

  'C'||upper(Translate(productname,'Ø. &/+-,%','O')) as tootjakood,

but it returns error

ERROR: column "Ø, 12.3/3mm" does not exist

testcase:

create temp table sales ( saledate date, productname char(20), quantity int );
insert into sales values ( '2016-1-1', 'Ø 12.3/3mm', 2);
insert into sales values ( '2016-1-1', '+-3,4%/3mm', 52);
insert into sales values ( '2016-1-3', '/3,2m-', 246);

do $do$


declare
voter_list text;
begin

create temp table myyk on commit drop as
select saledate as kuupaev,
  'C'||upper(Translate(productname,'Ø. &/+-,%','O')) as tootjakood, 
  sum(quantity)::int as kogus 
from sales
group by 1,2
;

drop table if exists pivot;

voter_list := (
    select string_agg(distinct tootjakood, ' ' order by tootjakood) from myyk
    );

execute(format('
    create table pivot (
kuupaev date,
        %1$s
    )', (replace(voter_list, ' ', ' integer, ') || ' integer')
));

execute (format($f$

  insert into pivot
        select
           kuupaev,
            %2$s
        from crosstab($ct$
            select
                kuupaev,tootjakood,kogus
            from myyk
            order by 1
            $ct$,$ct$
            select distinct tootjakood
            from myyk
            order by 1
            $ct$
        ) as (
            kuupaev date,
            %4$s
        );$f$,

        replace(voter_list, ' ', ' + '),
        replace(voter_list, ' ', ', '),
        '',
        replace(voter_list, ' ', ' integer, ') || ' integer'  -- 4.
    ));
end; $do$;

select * from pivot;

Postgres 9.1 is used.

score 1 · Accepted Answer · answered Feb 07 '16 at 18:42

You should use double quotes. Because you are using spaces to identify column separators, you should remove spaces from column names (or change the way of separators identification).

With

...
select saledate as kuupaev,
  format ('"%s"', replace (upper(productname), ' ', '')) as tootjakood, 
  sum(quantity)::int as kogus 
from sales
...

you'll get:

  kuupaev   | /3,2M- | +-3,4%/3MM | O12.3/3MM 
------------+--------+------------+-----------
 2016-01-01 |        |         52 |         2
 2016-01-03 |    246 |            |          
(2 rows)

Thank you very much. There is related question in http://stackoverflow.com/questions/35266931/returning-empty-data-from-dynamic-pivot-is-there-is-no-data — Andrus, Feb 08 '16 at 10:09

how to preserve column names on dynamic pivot

1 Answers1

Linked