SSIS：Oracle多行到一列输出而没有STRAGG

Question

当ID相同时，试图生成用逗号分隔的语言列表。

表格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

想要生成如下结果：

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

我尝试过的事情：

Stragg-SSIS给我“无效标识符”

SYS_Connect_By_Path-无法弄清楚如何将id转换为字符串语言。

有什么建议？

Answer 1

有多种方法可以执行字符串聚合以生成逗号分隔的列表-有关更多详细信息，请参见此链接。根据链接中的示例，尝试：

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;