DSXchange

Hi,

I have a Table which contains data in this format:

ID CITIES
12 Lahore,Karachi,Islamabad
23 Dubai, Abu Dhabi
45 New York
46 London

I need to separate this data like this:
ID CITIES
12 Lahore
12 Karachi
12 Islamabad
23 Dubai
23 Abu Dhabi
45 New York
46 London

Please suggest some ways in Data stage

Welcome.

Normally we'd start by asking things like 'what have you tried?' and 'what issues have you seen?'. Show us that you've at least tried to solve your puzzle before you came here and just posted input and output. In this case though, it's just a one word answer: pivot.

More specifically: a horizontal pivot of columns to rows. Oddly enough, there's a stage for that.

I use to have the same issue, i had that input :

FIELD
---------------
AAA;BBB;CCC;DDD
AAA;BBB;CCC;DDD


And i had to get that output :
FIELD
---------------
AAA
BBB
CCC
DDD
AAA
BBB
CCC
DDD

I do it by using a loop on a transformer (i think it's aviable since DataStage 8.5) :

I create a Stage Variable (CommaNum) with the value :
2 + Count(InputLink.FIELD,";")

My loop condition
@ITERATION < CommaNum

And my output field derivation
Field(InputLink.FIELD,";",@ITERATION)

Yes, that's a good alternative when you don't know how many columns may need to be pivoted or at least don't know the maximum number. And yes, available since 8.5.

I would have thought that the Column Import stage could have been involved here, prior to Pivot.