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
converting row to single column(i.e to a single row)
Moderators: chulett, rschirm, roy
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 20
- Joined: Mon Dec 05, 2005 8:07 pm
- Contact: