Home » SQL & PL/SQL » SQL & PL/SQL » multiple rows based on one column (Oracle 10g)
multiple rows based on one column [message #580410] Sun, 24 March 2013 05:14 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have one table , with one column having 2,3 or 4 machine codes , i need to display them as each row per machine code will it be possible to do as i have thousands of records similar to the test case and which i had to do it manually in excel and then upload it back.


create table ow_oper_setup (wo_no varchar2(12),mrk_no varchar2(20),pos_no varchar2(30),mc_code varchar2(60))

insert into ow_oper_setup VALUES ('1270','1270001','W165','IR HO BV ')

insert into ow_oper_setup VALUES ('1270','1270001','W1332','IR BV  ')

insert into ow_oper_setup values ('1270','1270001','W1367','RE HO SC BV ')

insert into ow_oper_setup values ('1270','1270001','W389','RE HO SC BV')

commit;

SELECT * FROM ow_oper_Setup;

WO_NO	MRK_NO	POS_NO	MC_CODE
1270	1270001	W165	IR HO BV 
1270	1270001	W1332	IR BV  
1270	1270001	W1367	RE HO SC BV 
1270	1270001	W389	RE HO SC BV

--i want the output in the following way or the same table data to be replaced as below


WO_NO	MRK_NO	POS_NO	MC_CODE
1270	1270001	W165	IR 
1270	1270001	W165	HO 
1270	1270001	W165	BV
1270	1270001	W1332	IR 
1270	1270001	W1332	BV 
1270	1270001	W1367	RE 
1270	1270001	W1367	HO 
1270	1270001	W1367	SC 
1270	1270001	W1367	BV  
1270	1270001	W389	RE 
1270	1270001	W389	HO 
1270	1270001	W389	SC 
1270	1270001	W389	BV 




Thanks and Regards
Arif






Re: multiple rows based on one column [message #580412 is a reply to message #580410] Sun, 24 March 2013 05:34 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
First, please read Normalization (the column MC_CODE is not atomic)

Second, please read Row Generator


Regards,
Dariyoosh

[Updated on: Sun, 24 March 2013 05:35]

Report message to a moderator

Re: multiple rows based on one column [message #580413 is a reply to message #580412] Sun, 24 March 2013 05:42 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks dariyoosh , i know its not normalized, but its just a sample data for working out with machines , and i need to upload and the target table has all the normalization.Can you please help me in generating the output as rows.
Re: multiple rows based on one column [message #580416 is a reply to message #580413] Sun, 24 March 2013 05:52 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sun, 24 March 2013 11:42
. . .Can you please help me in generating the output as rows . . .


Did you read the second link (Row generator) that I provided? There are many examples in that topic (with the advantage of taking into account different oracle versions). So take a look to that topic.


Regards,
Dariyoosh
Re: multiple rows based on one column [message #580418 is a reply to message #580416] Sun, 24 March 2013 06:02 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks dariyoosh , i checked the second link as i see there are lot of options for generating the rows , but how i can extract the data from one columns.
Re: multiple rows based on one column [message #580419 is a reply to message #580410] Sun, 24 March 2013 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ asked almost every week and not so far than a couple of days ago; please SEARCH before posting.

Read this post and the two subsequent ones.

Regards
Michel
Re: multiple rows based on one column [message #580428 is a reply to message #580419] Sun, 24 March 2013 06:23 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel for the help , i took that post example and applied on my data , but its giving the format and moreover i am not able to understand this cast multiset.


SQL>  break on mc_code dup skip 1
SQL>  col value format a10;
SQL> break on mc_code dup skip 1;
SQL> With data as (select mc_code, ','||mc_code||',' value from ow_oper_setup)
  2      select mc_code, column_value value_nb,
  3             substr(value,
  4                    instr(value, ',', 1, column_value)+1,
  5                    instr(value, ',', 1, column_value+1)-instr(value, ',', 1, column_value)-1
  6                   ) value
  7      from data,
  8           table(cast(multiset(select level from dual
  9                              connect by level < length(value)-length(replace(value,',')))
 10                as sys.odciNumberList))
 11                  order by 1, 2
 12  /

MC_CODE                                                        VALUE_NB
------------------------------------------------------------ ----------
VALUE
----------
IR BV                                                                 1
IR BV


IR HO BV                                                              1
IR HO BV


RE HO SC BV                                                           1

MC_CODE                                                        VALUE_NB
------------------------------------------------------------ ----------
VALUE
----------
RE HO SC B
V


RE HO SC BV                                                           1
RE HO SC B
V

Re: multiple rows based on one column [message #580430 is a reply to message #580428] Sun, 24 March 2013 06:33 Go to previous messageGo to next message
dariyoosh
Messages: 513
Registered: March 2009
Location: Iran / France
Senior Member
arif_md2009 wrote on Sun, 24 March 2013 12:23
. . . and moreover i am not able to understand this cast multiset . . .


Oracle online doc: Multiset Operators

Regards,
Dariyoosh


[EDITED by LF: fixed [quote] tags]

[Updated on: Tue, 26 March 2013 12:49] by Moderator

Report message to a moderator

Re: multiple rows based on one column [message #580432 is a reply to message #580430] Sun, 24 March 2013 06:43 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

Thanks dariyoosh , this seems to be huge topic , i will go through this and apply it on my data and check.THanks again for the great help.
Re: multiple rows based on one column [message #580434 is a reply to message #580428] Sun, 24 March 2013 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 57616
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
thanks michel for the help , i took that post example and applied on my data , but its giving the format and moreover i am not able to understand this cast multiset.


In your case the delimiter is NOT a comma but a space. You have to try to understand the post before you can use it.
Read the whole topic and you will able to understand the purpose of the cast multiset. Database SQL Reference is also there, refer to it for the point you don't know.

Regards
Michel
Re: multiple rows based on one column [message #580438 is a reply to message #580434] Sun, 24 March 2013 07:47 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks michel , i am sorry , next time i will read it carefully, i will substitute it with space and try and also go through the referred document.Many thanks again to all of you for helping me resolving this problem.
Re: multiple rows based on one column [message #580623 is a reply to message #580438] Tue, 26 March 2013 05:04 Go to previous messageGo to next message
saipradyumn
Messages: 175
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Arif ,


Followng is the simple query to achieve the desire results
SQL> 
SQL>    select distinct A.*,
  2                     substr(' ' || mc_code || ' ',
  3                            instr(' ' || mc_code || ' ', ' ', 1, level) + 1,
  4                            instr(' ' || mc_code || ' ', ' ', 1, level + 1) -
  5                            instr(' ' || mc_code || ' ', ' ', 1, level) - 1) code
  6       from OW_OPER_SETUP A
  7     connect by level <= (length(mc_code) - length(replace(mc_code, ' ')))
  8      order by pos_no, mc_Code;
 
WO_NO        MRK_NO               POS_NO                         MC_CODE                                                      CODE
------------ -------------------- ------------------------------ ------------------------------------------------------------ ---------------------------------1270         1270001              W1332                          IR BV                                                        BV
1270         1270001              W1332                          IR BV                                                        IR
1270         1270001              W1367                          RE HO SC BV                                                  BV
1270         1270001              W1367                          RE HO SC BV                                                  HO
1270         1270001              W1367                          RE HO SC BV                                                  RE
1270         1270001              W1367                          RE HO SC BV                                                  SC
1270         1270001              W165                           IR HO BV                                                     BV
1270         1270001              W165                           IR HO BV                                                     HO
1270         1270001              W165                           IR HO BV                                                     IR
1270         1270001              W389                           RE HO SC BV                                                  BV
1270         1270001              W389                           RE HO SC BV                                                  HO
1270         1270001              W389                           RE HO SC BV                                                  RE
1270         1270001              W389                           RE HO SC BV                                                  SC
 
13 rows selected
 
SQL> 


Please let me know if you have any concern

Thanks
SaiPradyumn
Re: multiple rows based on one column [message #580630 is a reply to message #580623] Tue, 26 March 2013 05:31 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks sai for the demonstration.
Previous Topic: ORA-01704 IN ORACLE
Next Topic: Extracting the data from web service response
Goto Forum:
  


Current Time: Mon Apr 21 05:33:23 CDT 2014

Total time taken to generate the page: 0.11424 seconds