0

I have a problem in OBI analytics. I'm using OBI 10g.

I have two columns: ID and supervisorID. As a result I want two columns: 1. the ID 2. and the concatenation of all the supervisorID of the ID in a second column.

So If I have ID following data:

ID     supervisorID
1      3
1      4
2      4

I want as a result:

ID     concat(supervisor)
1      3;4
2      4

I don't want to change anything at the RPD, so my question is if this is possible in analytics itself?

Gherbi Hicham
  • 2,416
  • 4
  • 26
  • 41
Bigjo
  • 613
  • 2
  • 10
  • 32

1 Answers1

0

This may be a crude answer, but I think it will work SELECT A.USER_ID, SUBSTR(A.SUPERVISORID, 0, LENGTH(A.SUPERVISORID) - 1) AS SUPERVISORID FROM (SELECT DISTINCT S.USER_ID, (xmltype(dbms_xmlgen.getXML('SELECT S1.SUPERVISORID||'';'' AS VAL_1 FROM SO_19 S1 WHERE S1.USER_ID = ''' || S.USER_ID ||'''')) . EXTRACT('//VAL_1/text()') .getStringval()) AS SUPERVISORID FROM SO_19 S) A

Rohan
  • 1,960
  • 3
  • 22
  • 30