Trafodion 行列转换

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

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

--Test group_concat functiondrop 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.