converting row to single column(i.e to a single row)

[dsstarter]

Hi All,
let me know if you could advise me on below question.

I have a table called tab1 with a column called name1 and this is a reference table, so it may not have records more than 1000 any time.

when i do select name1 from tab1 it will give some rows in output but i need to make all those row into one single row and use it for my processing......please let me know how can i do this.....
with out any basic routine

for ex: out put of my sql is(5) rows
AISHWARYA
MADHURI
SRIDEVI
PRIYANKA
RAANI

out put should be like below
AISHWARYAMADHURISRIDEVIPRIYANKARAANI
or
AISHWARYA,MADHURI,SRIDEVI,PRIYANKA,RAANI

both these formats are ok for me.....note: no of records in tab1 are not fixed..............

Starter

[kcbland]

So, you want an OCI/ODBC based reference lookup that will collapse an indeterminate number of values into a comma separated list?

Best answer: stored procedure

Second best answer: pre-collapse the reference rows via a job into a hashed file containing the required results

Reasons:

1. SQL cannot produce your result without help, meaning something like a function or a stored procedure is required
2. On a multi-row lookup (ODBC stage), you'll have to deal with aggregating the cartesian effect of the lookup to keep your primary input stream distinct (ugly exercise)
3. Nothing like you want exists in DS in any single stage.

[DSguru2B]

try this post
Click on me

I tried to do something similar.
In your case, you need to do your select as you are, let it pass through the transformer, and add a dummy column with a constant value, say 1.
go through my post (link is above). you can achieve what you are looking for.
cheers

[kris_daredevil]

use row merger to get the output u specified and the delimiter would be ,

[DSguru2B]

i highly doubt that a row merger would help, as a row merger just merges multiple columns into a single column on a single row.

[kcbland]

Are you all sure that your solutions will serve for a reference lookup?

[DSguru2B]

well, the original poster can do the ref. lookup in the sql itself, get the result in multiple rows. The solution advised by me is to merge the multiple rows into a single row.