Split a row into multiple rows

coolkhan08 · Post by **coolkhan08** » Sun Oct 17, 2004 12:02 am

Hi,
This is my first post, quite a happening forum I must say. My question is how can we split a row with more than 50 columns which has 6 colums to be split into 6 rows. Whats the best strategy for implementing this. I know it can be done in a multitude of ways.
Thanks
Sam

chulett · Post by **chulett** » Sun Oct 17, 2004 8:28 am

Welcome to our happening forum, Sam!

You've got a couple of primary choices to do what you need to do. Some of these answers may need to be tempered by the exact nature of your output rows - are they all of the same format / metadata or will they have different metadata? Your target - database, hash or sequential file - can make a difference as well.

Because you are on version 7.x you have access to the Row Splitter stage, which can be leveraged to create mutliple rows from a single row. You could then (possibly) use the Row Merger stage to put them back together again.

The 'traditional' answer is to take advantage of the record seperator character(s) for your platform and write out to a sequential file. The process is covered in a posting in the FAQ forum, here. In a nutshell, you write out one record with the various 'sub-records' delimited by CR/LF pairs for Windows (or just LFs for UNIX) and when the resulting file is opened afterwards, you magically have more rows that you started with.

So, there's a couple of different ways to investigate. On the off chance you're not aware of this, the pdf documentation for all of the plugin stages (Row Splitter/Merger) are installed automatically in the 'Docs' directory under your Client install directory.

coolkhan08 · Post by **coolkhan08** » Sun Oct 17, 2004 12:18 pm

Thanks Craig I dont have the splitter.pdf, I will try to find out more about the row splitter and merger stage. Can I have 6 hash files which would have all the coulmns and one of the 6 columns to be split into separate row then use funnel stage if its a parallel environment ?, how else we could do in server jobs other than row splitter and merger. I want all the coloumns and one of the 6 coloumns in the resultant data. A little more explanantion would be really helpful.
Sam.

vmcburney · Post by **vmcburney** » Sun Oct 17, 2004 4:59 pm

What you may need is a pivot via the pivot stage. Read the pivot stage documentation to see if it matches. I don't think the rowsplitter can turn a single row into multiple rows.

You're description is a bit vague, you have about 50 columns, you are trying to duplicate 44 of those columns 6 times over with the remaining 6 columns spread between those duplicates. Is this correct?

chulett · Post by **chulett** » Sun Oct 17, 2004 7:16 pm

Some random points...

You may not have the docs for the Row Splitter/Merger stages, as some of the early disks left them out. Vincent and I got them uploaded to Ascential's Developer Net some time ago and they are here if you have access to their file library.

Not really knowing the format or either your input or output records, I thought the Splitter might help. Still might help, it's hard to say.

When you mention the Funnel stage, you are venturing into the Parallel universe. There's a whole 'nother forum for that and a whole 'nother set of gurus over there, so it would probably be best to pursue that over there once this has played out.

Vince is right, you may be able to use the Pivot stage. Check the docs and see if it makes sense given your need... which is still a little hard to understand. It might help us all if you could clarify what it is you are trying to do. Perhaps a example of your input data and what the end result should look like?

coolkhan08 · Post by **coolkhan08** » Sun Oct 17, 2004 10:04 pm

Thanks vincent for your input. You got my requirement right, I want to duplicate the 44 columns 6 times with each row containing one of the remaining 6 columns.
Thanks again.
Sam

vmcburney · Post by **vmcburney** » Mon Oct 18, 2004 12:39 am

In that case the pivot stage should be able to handle this.

The other thing you could do is have 6 outputs from your transformer to 6 different files and concatenate them all together.

chulett · Post by **chulett** » Mon Oct 18, 2004 9:00 am

coolkhan08 wrote:I want to duplicate the 44 columns 6 times with each row containing one of the remaining 6 columns.

Then, as Vincent noted, you can use the Pivot stage. Bring all of your columns in on the Input side, and then for the Output side list the first 44 columns. In the 45th column, list your 'remaining 6 columns' comma seperated.

You may need to mark the first 44 as 'Keys' or as 'Grouped', I don't recall. The docs make it seem like it's not necessary, that it is driven by 'single derivation' versus 'multiple derivation', but I think it may be. Try it first without.

coolkhan08 · Post by **coolkhan08** » Mon Oct 18, 2004 9:19 am

That helps! thanks craig and vincent, hope to discuss more things in future.
Sam