multi lines --> single line ?

[htrisakti3]

if I have single column source that have 0,1,..N rows -- how can I transform into single line? ie:

source
AAA
BBB
CCC

output: AAA BBB CCC

[tcj]

The pivot stage should provide you with what you want.
Don't know if you need to install it as a plugin or not.

You should be able to read more information about the pivot stage in the plugin documentation. Should be in the docs folder in the datastage client folder on your pc.

[ray.wurlod]

What you are asking for is a vertical pivot. The Pivot stage performs horizontal pivot (one line to many), but definitely does not perform vertical pivots.

Several techniques are available, depending on exact circumstances. For example is there a small, finite number of columns to be generated in each cycle? Are the input data sorted?

Search the forum for an exact match on "vertical pivot" to learn more.

[tcj]

Sorry my mistake. Didn't have a copy of Datastage to double check if the pivot stage did both pivots.

[Gokul]

what type is the source is it?
1> If the source is Oracle then u can try the below query as user defined query and get the required result.

Code: Select all

  select max(decode(rownum,1,dname))||max(decode(rownum,2,dname))||max(decode(rownum,3,dname))  C  from dept

2> If the source is sequential and u r writing in a sequential file ,try with Line Termination set to none on the general tab of the o/p sequential file and delimeter set to ','.

(I tried this , the input rows were
AA
BB
CC

When i viewed the o/p file in the notepad the result was
"AA","BB","CC"
)

Thanks,
Gokul

[ray.wurlod]

I believe a more generic solution is needed, Gokul. For example

AA 1
BB 1
CC 1
AA 2
BB 2
CC 2

should yield

AA BB CC 1
AA BB CC 2

Your approach does not handle this; it can only ever generate one output row.

[Gokul]

Hi Ray,

Since the question was

if I have single column source that have 0,1,..N rows -- how can I transform into single line? ie:

source
AAA
BBB
CCC

output: AAA BBB CCC

I have consider a single source column. As always we hope to get a generic solution from you.

Gokul

[ray.wurlod]

You already did.

Vertical pivot.

Search is your friend.

[djm]

One option may be the Unix "paste" command. You can use it to roll-up multiple lines of data into a single line (I'm assuming that there are a consistent number of rows per set to be rolled up).

I don't have a Unix command session on hand to try this but some variation on

Code: Select all

paste -s -d '\t\t\n' filename > anotherfile

should roll-up repeating three rows of data into one row. Then take your input from "anotherfile" (columns would be tab-separated) and simply pick-out the columns you want.

This would address the generic question posed by ray.

David.

edit: replace uglier example with cleaner example, post running a "man paste".