Group field to comma separated String

[Former Member]

Hi

Using BODS, I'm trying to go from

+---+---+
| C | N |
+---+---+
| a | 1 |
| a | 2 |
| a | 3 |
| b | 2 |
| b | 4 |
+---+---+

to

+---+-------+
| C |  f(N) |
+---+-------+
| a | 1,2,3 |
| b |   2,4 |
+---+-------+

Meaning, it should group using the column C and have as another column the concatenation of column N separated with commas.

In another word, I would like to do the opposite of this : [split a comma separated String into multiple rows|http://wiki.sdn.sap.com/wiki/pages/viewpage.action?pageId=49414399]

I tried but so far no luck in doing so. Have someone have an idea?

Regards

[Former Member]

This message was moderated.

[Former Member]

I finally did it using the load_to_xml!

Pfiew that was some work.

Here is my solution: [https://wiki.sdn.sap.com/wiki/display/stage/Howtogroupfieldsintocommaseparated+values]

[paul_kessler]

Thanks for sharing your solution. The Reverse Pivot transform will also transpose the data, but it will place your row data into discrete output columns. A query transform can then be used to concatenate the data into a single field.

[Former Member]

Hi

I can't see how this can be done using the Reverse_Pivot transform.

I believe, correct me if I'm wrong, that you need to enumerate all axis values in order to create the corresponding pivoted columns.

I posted [a comment on the previous wiki entry|https://wiki.sdn.sap.com/wiki/pages/commentstab/viewcomments.action?pageId=198738912&focusedCommentId=198968299#comment-198968299] showing how I think it can be done using Reverse_Pivot but the limitation is that you should be aware of a maximum number of items to use this method. In my case I d'ont know it and the data source shows several hundreds of items, which would really be a boring thing to implements using this method.

I might have overlooked something because you are talking about discrete columns...

Regards

Guillaume

[Former Member]

Giving more information, I tried to do the opposite of what's done in the quoted article above meaning make a nested table with the N values then convert it to an XML string.

So far it works but I can't find a way to convert the string outputted by the DataFlow I created using the load_to_xml function::

<?xml version="1.0" encoding="UTF-8"?>
<Nest>
<C>A</C>
<Ns><N>1</N>
</Ns>
<Ns><N>2</N>
</Ns>
<Ns><N>3</N>
</Ns>
</Nest>

Including carriage return, see the [DataFlow screenshot with result|http://hfr-rehost.net/http://self/pic/9d9a432ad1c2aed1414a24e8c7ef66cf98e9cef4.png]

to comma separated values : 1,2,3

[paul_kessler]

The Reverse Pivot transform should do this for you.

If you do not have access to the DI transforms, another option is to use the Match transform to match on the first column then use the Best Record option to append the column 2 values to the master record.

You might also be able to do this via a SQL comand.

Paul

[Former Member]

My first guess was indeed using the Reverse Pivot but I don't know the maximum number of items each group can have.

This is my fallback method using something like 200 pivot columns but it seems bad programming...