1.函数定义
TDstats.udfConcat为Teradata自带UDF,定义如下:
show function tdstats.udfconcat; REPLACE FUNCTION tdstats.UDFCONCAT
(aVarchar VARCHAR(128) CHARACTER SET UNICODE)
RETURNS VARCHAR(10000) CHARACTER SET UNICODE
CLASS AGGREGATE (20000)
SPECIFIC udfConcat
LANGUAGE C
NO SQL
NO EXTERNAL DATA
PARAMETER STYLE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
EXTERNAL NAME 'SL!staudf!F!udf_concatvarchar'
2.功能与使用
(1)函数功能:将多行转换为一行,以逗号分隔。
(2)用户赋权
grant exec function on TDstats.udfconcat to XXXX;
(3)使用方法:
#数据准备
CREATE MULTISET TABLE udfconcat_test
(
userid varchar(100) CHARACTER SET LATIN NOT CASESPECIFIC,
role_name VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC ,
role_id VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC
)
no PRIMARY INDEX;
insert into udfconcat_test values('','管理员','');
insert into udfconcat_test values('','编辑','');
insert into udfconcat_test values('','超级管理员','');
insert into udfconcat_test values('','普通会员','');
insert into udfconcat_test values('','高级会员','');
insert into udfconcat_test values('',null,null);
#函数调用
sel userid, cast(tdStats.udfconcat(role_name) as varchar(500)) from udfconcat_test group by 1 order by 1;
#最终结果
1 "管理员","编辑","超级管理员"
2 "普通会员"
3 "高级会员"
4
(4)注意事项:
数据量太大时存在spool不足的风险,执行效率也非常低。
此外注意返回值为VARCHAR(10000),返回结果需要根据实际结果值长度使用cast缩短返回结果的长度。
3.多行多列间转换