我们在处理数据的时候 经常会遇到将数据行列转换的情形,如本文章最下面2图分别代表要转换的2中数据格式,在TD里面,它给我们提供了一个很好的迭代方法(WITH RECURSIVE)帮我们来解决这个问题。
我们先说由数据1转换到数据2的情况(即行转列),废话不多说,直接看例子
假设数据1对应表 mart_name. temp3
mart_name. temp2 为临中间时表
CREATE TABLE mart_name. temp2
(id VARCHAR(8)
,prod_cd VARCHAR(50)
,i integer
);
CREATE TABLE mart_name. temp3
(id VARCHAR(8)
,prod_cd VARCHAR(50)
);
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000001','房贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000001','汽车贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000001','助业贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000002','房贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000002','消费贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000003','房贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000004','消费贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000005','房贷');
INSERT INTO mart_name.temp3 (id, prod_cd) VALUES ('10000005','消费贷');--mart_name. temp2的目的在于:把每个id所属产品编排一个连续的序号,且序号都从1开始
insert into mart_name.temp2
SELECT
root.id
,root.prod_cd
,rank(prod_cd) i
FROM mart_name.temp3 root
group by 1;--在这里用递归查询的方法,就能得到数据2
WITH RECURSIVE temp_table (id1
,prod_cd , i ) AS
( SELECT
root.id
,root.prod_cd
,i
FROM mart_name.temp2 root
where i =1
UNION ALL
SELECT indirect.id
,indirect.prod_cd||','||direct.prod_cd
,indirect.i
FROM temp_table direct
, mart_name.temp2 indirect
WHERE
direct.id1 = indirect.id
and direct.i+1= indirect.i
)sel * from temp_table QUALIFY ROW_NUMBER() OVER(PARTITION BY id1 ORDER BY i DESC) = 1;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24362799/viewspace-722425/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24362799/viewspace-722425/