SSIS: Oracle Multiple rows to one column output without STRAGG

Question

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

Table 1:

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

Table 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 gives me "Invalid Identifier"

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

Any suggestions?

OMG Ponies · Accepted Answer · 2010-01-20 20:11:20Z

6

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;

answered Jan 20, 2010 at 20:11

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Martin Schapendonk · Accepted Answer · 2011-06-10 06:18:23Z

2

For 11g, check out the listagg function

select t1.id, listagg(t2.language, ', ') within group (order by t2.language)
from t1, t2
where t1.langId = t2.id
group by t1.id;

answered Jun 10, 2011 at 6:18

Martin Schapendonk

13.6k3 gold badges22 silver badges26 bronze badges

Collectives™ on Stack Overflow

SSIS: Oracle Multiple rows to one column output without STRAGG

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related