Teradata:将重复值转换为逗号分隔字符串的结果 [英] Teradata: Results with duplicate values converted into comma delimited strings
问题描述
我有一个典型的表格,其中每一行代表一个客户 - 产品持有量.如果一个客户有多个产品,那么会有多行具有相同的客户 ID.我正在尝试将其汇总起来,以便每个客户都由一行表示,所有产品代码都连接在一个逗号分隔的字符串中.下图说明了这一点
I have a typical table where each row represents a customer - product holding. If a customer has multiple products, there will be multiple rows with the same customer Id. I'm trying to roll this up so that each customer is represented by a single row, with all product codes concatenated together in a single comma delimited string. The diagram below illustrates this
在谷歌搜索之后,我设法使用 XMLAGG 函数让它工作 - 但这仅适用于一小部分数据,当扩展时 Teradata 抱怨假脱机空间"用完 - 所以我认为它不是很高效.
After googling this, I managed to get it to work using the XMLAGG function - but this only worked on a small sample of data, when scaled up Teradata complained about running out of 'spool space' - so I figure it's not very efficient.
有人知道如何有效地实现这一目标吗?
Does anyone know how to efficiently achieve this?
推荐答案
较新版本的 Teradata 支持 NPath
,可用于此目的.你必须习惯语法,它是一个表运算符 :-)
Newer versions of Teradata support NPath
, which can be used for this. You have to get used to the syntax, it's a Table Operator :-)
例如这将返回系统中每个表的列列表:
E.g. this returns the column list for each table in your system:
SELECT *
FROM
NPath(ON(SELECT databasename, tablename, columnname, columnid
FROM dbc.columnsV
) AS dt -- input data
PARTITION BY databasename, tablename -- group by columns
ORDER BY columnid -- order within list
USING
MODE (NonOverlapping) -- required syntax
Symbols (True AS F) -- every row
Pattern ('F*') -- is returned
RESULT(First (databasename OF F) AS DatabaseName, -- group by column
First (tablename OF F) AS TableName, -- group by column
Count (* OF F) AS Cnt,
Accumulate(Translate(columnname USING unicode_to_latin) OF ANY (F)) AS ListAgg
)
);
应该比 XMLAgg 好.
Should be waaaaaay better than XMLAgg.
这篇关于Teradata:将重复值转换为逗号分隔字符串的结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!