SSIS:Oracle 多行到一列输出,无需 STRAGG [英] SSIS: Oracle Multiple rows to one column output without STRAGG
本文介绍了SSIS:Oracle 多行到一列输出,无需 STRAGG的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
希望在 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
我尝试过的事情:
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;
这篇关于SSIS:Oracle 多行到一列输出,无需 STRAGG的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文