Trafodion 行列转换

Trafodion中提供一个函数叫做group_concat，用于把多列转成一行，具体语法请参考官方手册 http://trafodion.incubator.apache.org/docs/sql_reference/index.html#group_concat_function

下面是自己作的一个小试验测试group_concat基本功能，

--Test group_concat function

drop table if exists employee;
create table employee (
empno int not null,
attr char(20) not null,
value char(20),
primary key (empno, attr)
);

insert into employee values(1,'name','Kevin');
insert into employee values(2,'name','Eason');
insert into employee values(3,'name','Hank');
insert into employee values(1,'address','Address A');
insert into employee values(2,'address','Address B');
insert into employee values(3,'address','Address C');
insert into employee values(1,'title','Junior Engineer');
insert into employee values(2,'title','Senior Engineer');
insert into employee values(3,'title','Architecture');

cqd mode_special_4 'on';

select * from employee order by empno,attr;

select empno, 
       trim(substr(k_v from position('name' in k_v)+20 for 20)) as name,
       trim(substr(k_v from position('address' in k_v)+20 for 20)) as address,
       trim(substr(k_v from position('title' in k_v)+20 for 20)) as title
  from (select empno, group_concat(attr||value order by attr separator '') as k_v
          from employee
        group by empno) as foo;

运行结果如下，

SQL>select * from employee order by empno,attr;

EMPNO       ATTR                 VALUE
----------- -------------------- --------------------
          1 address              Address A
          1 name                 Kevin
          1 title                Junior Engineer
          2 address              Address B
          2 name                 Eason
          2 title                Senior Engineer
          3 address              Address C
          3 name                 Hank
          3 title                Architecture

--- 9 row(s) selected.

SQL>select empno,
           trim(substr(k_v from position('name' in k_v)+20 for 20)) as name,
           trim(substr(k_v from position('address' in k_v)+20 for 20)) as address,
           trim(substr(k_v from position('title' in k_v)+20 for 20)) as title
  from (select empno, group_concat(attr||value order by attr separator '') as k_v
                  from employee
                group by empno) as foo;

EMPNO       NAME                 ADDRESS              TITLE
----------- -------------------- -------------------- --------------------
          1 Kevin                Address A            Junior Engineer
          2 Eason                Address B            Senior Engineer
          3 Hank                 Address C            Architecture

--- 3 row(s) selected.