连接来自 Teradata SQL 中多条记录的字符串 [英] Concatenate strings from multiple records in Teradata SQL

问题描述

我有一份在不同州开展业务的商家名单.

I have a list of merchants that do business in different states.

Merch    State

A          NC

A          FL

B          CA

B          VA

我想按 Merch 分组而不是返回四个记录，而是连接状态的字符串，以便输出看起来像

Instead of returning four records I want to group by Merch but concatenate the strings of the states so that the output looks like

Merch      States

A           NC,FL

B           CA,VA

我在为我的问题翻译此答案中的回复时遇到了很多麻烦连接/聚合字符串的最佳方式

I'm having a lot of trouble translating the response in this answer for my issue Optimal way to concatenate/aggregate strings

我也无法让 String_agg 工作，我不确定它是否适用于 Teradata.https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

I also cannot get String_agg to work, I'm not sure it works in Teradata. https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

推荐答案

您可以使用 Teradata 中的 xml_agg() 内置函数.这不能很好地从 SQL Server 转换，这就是为什么您在这两个链接上遇到问题的原因.

You can use the xml_agg() built in function in Teradata. This doesn't translate well from SQL Server which is why you are having issues with those two links.

SELECT merch, TRIM(TRAILING ',' FROM (XMLAGG(States || ',' ORDER BY States) (VARCHAR(500))))
FROM yourtable
GROUP BY 1;

这篇关于连接来自 Teradata SQL 中多条记录的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！