REGEX to merge rows into comma seperated list

[billabang]

Hi

I was wondering if after performing a select for one column from a TABLE INPUT step if I could convert this column into a comma separated list for the next step?

So:

FIELD1
FIELD2
...
FIELDN

would become:

FIELD1,FIELD2,FIELD3,...,FIELDN

Ready for the next steps usage.

I guessed that REGEX might be needed but I'm not 100% sure.

Any help is much appreciated

Thanks

Bill

[billabang]

Fixed my issue rather quickly with something I didn't expect to work.....

In my TABLE INPUT I used the following SQL:


DECLARE @ret VARCHAR(8000)

SELECT @ret = COALESCE(@ret, '') + attributename + ','
FROM metaattributes
WHERE tablename = '${TABLENAME}';

SET @ret = LEFT(@ret, LEN(@ret) - 1)

SELECT @ret as 'attributes';
And voila! It gives me back a CSV list in one field named attributes.

Sorry to have posted!

[gutlez]

You can do the same thing with the Group By step...

Sort the Table Input based on the key(s) you want, and then use the Group By step to join them all with whatever delimiter you want. "Concatenate Strings Separated by" option

[billabang]

Thanks Gutlez

[billabang]

Hi Gutlez

I've decided to try and use the group by step as you mentioned in another part of my solution design however the option you mention "Concatenate Strings Separated by" doesn't appear to be available.

Is this in a later version that 3.2?

Thanks

Bill

[gutlez]

3.2.0-Stable / 3.2.0-GA

In the "Type" Menu, it's the last item on the list for me.

[billabang]

Thanks again for replying Gutlez. I think I'm either doing something stupid, am very tired or again am just doing something stupid but I've attached a screenshot of my group by step options.

My Kettle Help -> About states version 3.2.0

Any ideas?

[billabang]

Yep been a complete idiot.

FOund it!
:o