【Teradata SQL】多行转一列函数TDStats.udfConcat

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('1','管理员','1');
insert into udfconcat_test values('1','编辑','2');
insert into udfconcat_test values('1','超级管理员','3');
insert into udfconcat_test values('2','普通会员','4');
insert into udfconcat_test values('3','高级会员','5');
insert into udfconcat_test values('4',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.多行多列间转换

行转列、列转行的简单实现