由网友(高冷小公举=￣ω￣=)分享简介：展望生成一个逗号分隔的语言列表时，ID是一样的。Looking to generate a comma separated list of languages when the ID is the same.表1：ID | LangID1 11 21 32 32 43...

展望生成一个逗号分隔的语言列表时，ID是一样的。

Looking to generate a comma separated list of languages when the ID is the same.

表1：

ID | LangID
1    1
1    2
1    3
2    3
2    4
3    1

表2：

ID | Language
1    English
2    Spanish
3    French
4    German

要生成看起来像结果：

Want to generate results that look like:

ID | Languages
1    English, Spanish, French
2    French, German
3    English

我曾尝试

东西：

Things I have tried:

Stragg - SSIS给我无效的

Stragg - SSIS gives me "Invalid Identifier"

SYS_CONNECT_BY_PATH - 无法弄清楚如何加入的ID转换成字符串语言

SYS_Connect_By_Path - Can't figure out how to join the ids to convert to the string languages.

有什么建议？

推荐答案

有不同的方法来执行字符串的聚合，从而产生一个逗号分隔的列表 - 看到的这个链接了解更多详细信息。根据链接中的例子，请尝试：

There are various ways to perform string aggregation to produce a comma separated list - see this link for more details. Based on the example in the link, try:

SELECT x.id,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(x.language,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees
  FROM (SELECT a.id,
               b.language,
               ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.language) AS curr,
               ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.language) -1 AS prev
          FROM TABLE_1 a
          JOIN TABLE_2 b ON b.id = a.langid) x
GROUP BY x.id
CONNECT BY prev = PRIOR curr AND x.id = PRIOR x.id
START WITH curr = 1;