Split the data in the row seperated by comma
-
I have 1 table field that contains values separated by “;”
Number of values in each field of the records is unknown in advance (can be 0 to 20+)
An example would be:
ID COVER_ID
1. BR-SEC-010;BR-SEC-020
2. BR-SEC-010
3
4 BR-SEC-030
I would need a sql query to split the records based on the cover id, without using PL/SQL.
The expected result of the query would be
1 BR-SEC-010
1 BR-SEC-020
2 BR-SEC-010
3
4 BR-SEC-030
Would you have an idea ?
-
kamala_moorthy (6/17/2011)
...without using PL/SQL...You might want to try a good Oracle forum or maybe give "Ask Tom" a try. This forum is heavily T-SQL oriented.
I am curious, though. Why do you need to do this using standard SQL instead of using the richness of PL/SQL?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As a bit of a side bar, using the following as search term in Google. It produces some pretty good hits according to the returned titles...
split CSV in OracleJust replace "," with ";" in the answers given. Of course, I didn't check to see if they were non-PL/SQL answers or not. I'll leave that up to you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff.
Regards,
Kamal
-
kamala_moorthy (6/20/2011)
Thanks Jeff.Regards,
Kamal
You're welcome. But you still didn't answer my question and this IS a two way street here.
Why do you need to do this using standard SQL instead of using the richness of PL/SQL? --Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
The requirement is for generating XL Reports from Quality Center 9.2.
The Quality Center report generator does not support PL/SQL, Supports only SQL commands.
Hence I requested only SQL query to do this.
Thanks for your suggestions.
Regards,
Kamal
-
Thanks, Kamal.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply