How do I create a string of information from a single field?

[#1]

I have individual products that have separate entries in our database for each Category the product is in:

Product	Category
A	1
A	2
A	3

I want a single entry for this information in a chart I'm building that just separates each Category by a comma. Something more like below. Preferably this would be built in the script so it loads in as it's own field.

Product	Categories
A	1,2,3
B	1,5,10

Any suggestions?

[Anonymous · Accepted solution]

Use concat() function.  In the script:

LOAD

     Product,

     concat(distinct Category, ', ') as Categories

FROM (or RESIDENT...

GROUP BY Product;

[stigchel]

In the chart with Product as Dimension use

Concat(DISTINCT Category,',')

As expression

[maxgro]

s2:

load * inline [

Product, Categories

A, "1,2,3"

B, "1,5,10"

];

f2:

NoConcatenate

load Product, SubField(Categories, ',') as Category

Resident s2;