如何在动态数据透视表上保留列名 [英] how to preserve column names on dynamic pivot

问题描述

销售数据包含可以包含任何字符的动态产品名称.

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()用于删除不良字符.

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, 

代替

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

但它返回错误

错误:列Ø，12.3/3mm"不存在

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

测试用例:

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.

Postgres 9.1 is used.

推荐答案

您应使用双引号.由于您使用空格来标识列分隔符，因此应从列名称中删除空格(或更改分隔符标识的方式).

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).

使用

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

您将获得:

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

这篇关于如何在动态数据透视表上保留列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！