Home » SQL & PL/SQL » SQL & PL/SQL » split data of one column on bases of another column (multi merge of an IM illiterate)
split data of one column on bases of another column (multi merge of an IM illiterate) [message #270491] Thu, 27 September 2007 03:32 Go to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all,

I need some help,
i have a select query that gives me output like this

1	ACCLTDEQNR	N	1150.55000
2	ACCLTDEQNR	B	1010.50000
3	HDFBANEQNR	N	0.00000
4	HDFBANEQNR	B	1097.25000
5	ICIBANEQNR	N	0.00000
6	ICIBANEQNR	B	834.20000
7	LARTOUEQNR	N	0.00000
8	LARTOUEQNR	B	2451.00000
9	TCSLTDEQNR	N	1001.70000
10	TCSLTDEQNR	B	1017.50000



but now i need the output like this

1	ACCLTDEQNR	N	1150.55000
4	HDFBANEQNR	B	1097.25000
6	ICIBANEQNR	B	834.20000
8	LARTOUEQNR	B	2451.00000
9	TCSLTDEQNR	N	1001.70000


the second output ids based that if 'N' have then that value else the value of 'B'

help me
i tried it by case statement but it was unsuccessful
tell me how should i put condition so i can get it

Regards
sonal

[mod-edit] illiterate IM speak removed.

[Updated on: Thu, 27 September 2007 08:22] by Moderator

Report message to a moderator

Re: Select Query [message #270493 is a reply to message #270491] Thu, 27 September 2007 03:39 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
the second output ids based that if 'N' have then that value else the value of 'B'

What does it mean?
Re: Select Query [message #270495 is a reply to message #270491] Thu, 27 September 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
with 
  data as (
    select col1, col2, col3, col4,
           row_number () over (partition by col2 order by col3 desc) rn
    from tab
  )
select col1, col2, col3, col4
from data
where rn = 1
/

Regards
Michel
Re: Select Query [message #270504 is a reply to message #270493] Thu, 27 September 2007 03:58 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
hi thanks for reply

the second output ids based that if 'N' have then that value else the value of 'B'


means if flg N have data in next column then show that values else show the values of flg B have data in next column

plzzzzzzzzzzzzzz reply soon

sonal
Re: Select Query [message #270505 is a reply to message #270504] Thu, 27 September 2007 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Adding more z'z onto the hideous IMSpeak plz does not increase my desire to help you.
Re: Select Query [message #270510 is a reply to message #270504] Thu, 27 September 2007 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I already reply.
plzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz read and reply soooooooooooooooon.

Regards
Michel

Re: Select Query [message #270525 is a reply to message #270510] Thu, 27 September 2007 06:02 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
hi
can someone suggest me other way out
for this problem

plzzzz help

sonal
Re: Select Query [message #270529 is a reply to message #270525] Thu, 27 September 2007 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why?

Regards
Michel
split data of one column on the bases of another column [message #270536 is a reply to message #270491] Thu, 27 September 2007 07:24 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all

how to split data of one column on the bases of another column?

for example

col1 col2   col3 
 1     A       120
 1     B       140
 2     A         0
 2     B       165  
 3     A         0        
 3     B       156
 4     A       140
 4     B       125


i want to select all col3 data for col2 value 'A' but if the data in col2 id 0 fro col2 value 'A' then it should take value of those where col2 value is 'B'

so output should look like

col1 col2   col3 
 1     A       120
 2     B       165  
 3     B       156
 4     A       140



please give me suggetion for puting condition
plzzzzzzzzz help

sonal

Re: split data of one column on the bases of another column [message #270539 is a reply to message #270536] Thu, 27 September 2007 07:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't multipost the same question.

Regards
Michel
Re: Select Query [message #270541 is a reply to message #270529] Thu, 27 September 2007 07:38 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
please have a look on my query

        select MWC_ESMM_SEM_SMST_SECURITY_ID,MWC_EXCH_ID,MWC_BUY_PRICE 
        from MKT_WATCH m
        where MWC_ESMM_SEM_SMST_SECURITY_ID in (select    CTQ_SECURITY_ID c FROM CC_TRANSACTION_QTY C
        where C.CTQ_CLIENT_ID = '330141')
        ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID DESC


and output is


 MWC_ESMM_SEM_SMST_SECURITY_ID	MWC_EX_ID   MWC_BUY_PRICE
1	TCSLTDEQNR	         N      	1001.70000
2	TCSLTDEQNR	         B       	1017.50000
3	LARTOUEQNR	         N      	0.00000
4	LARTOUEQNR	         B      	2451.00000
5	ICIBANEQNR	         N      	0.00000
6	ICIBANEQNR	         B      	834.20000
7	HDFBANEQNR	         B      	1097.25000
8	HDFBANEQNR	         N      	0.00000
9	ACCLTDEQNR	         B      	1010.50000
10	ACCLTDEQNR	         N      	1150.55000



i am not able to encorporate your code wirh this
plz if you can encorporate it then it will be the great help
if you can't please suggest me

sonal
Re: Select Query [message #270543 is a reply to message #270541] Thu, 27 September 2007 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where did you fail?
And don't use IM speak.

Regards
Michel
Re: Select Query [message #270550 is a reply to message #270541] Thu, 27 September 2007 08:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Try removing COL1 from Michels query, and replacing COL2, COL3, COL4 with MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EX_ID, MWC_BUY_PRICE.

The way you have shown your results makes it look like there are 4 columns being displayed.
Re: split data of one column on bases of another column (multi merge of an IM illiterate) [message #270835 is a reply to message #270491] Fri, 28 September 2007 02:29 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi

may be something like this should help you.
with data as 
  (
  select 1 COL1,'ACCLTDEQNR' COL2,'N' COL3,1150.55000 COL4 FROM DUAL UNION ALL
  select 2,'ACCLTDEQNR','B',1010.50000 FROM DUAL UNION ALL
  select 3,'HDFBANEQNR','N',0.00000 FROM DUAL UNION ALL
  select 4,'HDFBANEQNR','B',1097.25000 FROM DUAL UNION ALL
  select 5,'ICIBANEQNR','N',0.00000 FROM DUAL UNION ALL
  select 6,'ICIBANEQNR','B',834.20000 FROM DUAL UNION ALL
  select 7,'LARTOUEQNR','N',0.00000 FROM DUAL UNION ALL
  select 8,'LARTOUEQNR','B',2451.00000 FROM DUAL UNION ALL
  select 9,'TCSLTDEQNR','N',1001.70000 FROM DUAL UNION ALL
  select 10,'TCSLTDEQNR','B',1017.50000 FROM DUAL
  )

select col1,col2,col3,col4 from 
  (select col1,col2,col3,col4,row_number()over(partition by col2 order by col3 desc)r
    from data where col4 <>0
  )
where r=1


regards,
Re: split data of one column on bases of another column (multi merge of an IM illiterate) [message #270898 is a reply to message #270835] Fri, 28 September 2007 05:22 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
with 
  data as (
    select MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EXCH_ID, MWC_BUY_PRICE,
           row_number () over (partition by MWC_ESMM_SEM_SMST_SECURITY_ID order by MWC_EXCH_ID desc) rn
    from MKT_WATCH 
        where MWC_ESMM_SEM_SMST_SECURITY_ID in (select CTQ_SECURITY_ID c FROM CC_TRANSACTION_QTY C
        where C.CTQ_CLIENT_ID = '330141')
        ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID,mwc_exch_id DESC
  )
select  MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EXCH_ID, MWC_BUY_PRICE
from data
where rn = 1



thia query does not give me any output onlu column names are displayed

[Updated on: Fri, 28 September 2007 05:40]

Report message to a moderator

Re: split data of one column on bases of another column (multi merge of an IM illiterate) [message #270902 is a reply to message #270898] Fri, 28 September 2007 05:34 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

this link might help you understand

http://www.psoug.org/reference/with.html



regards,
Re: split data of one column on bases of another column (multi merge of an IM illiterate) [message #270925 is a reply to message #270902] Fri, 28 September 2007 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For me until you stop posting irrelevant German postal code, I'll not answer you.

Regards
Michel
Re: split data of one column on bases of another column (multi merge of an IM illiterate) [message #270927 is a reply to message #270902] Fri, 28 September 2007 07:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks ok.

Run the Data1 query by itself - does it return any rows?
Re: split data of one column on bases of another column (multi merge ) [message #271072 is a reply to message #270927] Sat, 29 September 2007 00:18 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all ,

I tried it by changed alias that was data1 still not getting any output

is there some other way to do it ?
if yes, then please tell me


sonal

[Updated on: Sat, 29 September 2007 05:33]

Report message to a moderator

Re: split data of one column on bases of another column (multi merge ) [message #271290 is a reply to message #271072] Mon, 01 October 2007 02:58 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Sorry, I can't understand what you're saying.
I shall therefore fall back on the tried and tested English approach of repeating myself slower and louder.

If your query is returning no results, I strongle suspect that this query is returning no rows:
select MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EXCH_ID, MWC_BUY_PRICE,
           row_number () over (partition by MWC_ESMM_SEM_SMST_SECURITY_ID order by MWC_EXCH_ID desc) rn
    from MKT_WATCH 
        where MWC_ESMM_SEM_SMST_SECURITY_ID in (select CTQ_SECURITY_ID c FROM CC_TRANSACTION_QTY C
        where C.CTQ_CLIENT_ID = '330141')
        ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID,mwc_exch_id DESC

If this is the case, that would mean that this is also returning no rows:
SELECT *
from MKT_WATCH 
where MWC_ESMM_SEM_SMST_SECURITY_ID in (select CTQ_SECURITY_ID c FROM CC_TRANSACTION_QTY C
                                        where C.CTQ_CLIENT_ID = '330141')

Now at this point you're going to have to go and work out what you've done wrong, so get back to us when you've made this query work.
Re: split data of one column on bases of another column (multi merge of an IM illiterate) [message #271334 is a reply to message #270491] Mon, 01 October 2007 05:46 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
P.S : Oops !!! Sorry i didn't see the post from dhananjay. My sincere apologies... So i removed it.
Cheers
Raj

[Updated on: Mon, 01 October 2007 05:49]

Report message to a moderator

Re: split data of one column on bases of another column (multi merge ) [message #271336 is a reply to message #271290] Mon, 01 October 2007 06:06 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
please have a look on my query

        select MWC_ESMM_SEM_SMST_SECURITY_ID,MWC_EXCH_ID,MWC_BUY_PRICE 
        from MKT_WATCH m
        where MWC_ESMM_SEM_SMST_SECURITY_ID in (select    CTQ_SECURITY_ID c FROM CC_TRANSACTION_QTY C
        where C.CTQ_CLIENT_ID = '330141')
        ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID DESC



and output is

 MWC_ESMM_SEM_SMST_SECURITY_ID	MWC_EX_ID   MWC_BUY_PRICE
1	TCSLTDEQNR	         N      	1001.70000
2	TCSLTDEQNR	         B       	1017.50000
3	LARTOUEQNR	         N      	0.00000
4	LARTOUEQNR	         B      	2451.00000
5	ICIBANEQNR	         N      	0.00000
6	ICIBANEQNR	         B      	834.20000
7	HDFBANEQNR	         B      	1097.25000
8	HDFBANEQNR	         N      	0.00000
9	ACCLTDEQNR	         B      	1010.50000
10	ACCLTDEQNR	         N      	1150.55000



Re: split data of one column on bases of another column (multi merge ) [message #271340 is a reply to message #271336] Mon, 01 October 2007 06:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the results you've posted, I'd say everything is working fine, and you're getting data back:
create table simple_test (MWC_ESMM_SEM_SMST_SECURITY_ID varchar2(30),MWC_EXCH_ID varchar2(1),MWC_BUY_PRICE number);

insert into simple_test values ('TCSLTDEQNR','N',1001.70000);
insert into simple_test values ('TCSLTDEQNR','B',1017.50000);
insert into simple_test values ('LARTOUEQNR','N',0.00000);
insert into simple_test values ('LARTOUEQNR','B',2451.00000);
insert into simple_test values ('ICIBANEQNR','N',0.00000);
insert into simple_test values ('ICIBANEQNR','B',834.20000);
insert into simple_test values ('HDFBANEQNR','B',1097.25000);
insert into simple_test values ('HDFBANEQNR','N',0.00000);
insert into simple_test values ('ACCLTDEQNR','B',1010.50000);
insert into simple_test values ('ACCLTDEQNR','N',1150.55000);

with 
  data as (
    select MWC_ESMM_SEM_SMST_SECURITY_ID
          ,MWC_EXCH_ID
          ,MWC_BUY_PRICE,
           row_number () over (partition by MWC_ESMM_SEM_SMST_SECURITY_ID order by MWC_EXCH_ID desc) rn
    from simple_test
    ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID,mwc_exch_id DESC
  )
select  MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EXCH_ID, MWC_BUY_PRICE
from data
where rn = 1;

MWC_ESMM_SEM_SMST_SECURITY_ID  MWC_EXCH_ID MWC_BUY_PRICE          
------------------------------ ----------- ---------------------- 
ACCLTDEQNR                     N           1150.55                
HDFBANEQNR                     N           0                      
ICIBANEQNR                     N           0                      
LARTOUEQNR                     N           0                      
TCSLTDEQNR                     N           1001.7
Re: split data of one column on bases of another column (multi merge ) [message #271365 is a reply to message #271340] Mon, 01 October 2007 07:53 Go to previous messageGo to next message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi

I thing you have not understood the requirment of writing this query

I want to show data where MWC_EXCH_ID = 'N' and MWC_BUY_PRICE is not equal to 0
if MWC_EXCH_ID = 'N' is there and MWC_BUY_PRICE is equal to 0
then value of MWC_BUY_PRICE should be shown where
MWC_EXCH_ID = 'B'

I don't want 0 as MWC_BUY_PRICE in any condition

so output should be like this
1	ACCLTDEQNR	N	1150.55000
4	HDFBANEQNR	B	1097.25000
6	ICIBANEQNR	B	834.20000
8	LARTOUEQNR	B	2451.00000
9	TCSLTDEQNR	N	1001.70000



please let me know if there is any confusion


Beast Regards
Sonal

Re: split data of one column on bases of another column (multi merge ) [message #271380 is a reply to message #271365] Mon, 01 October 2007 08:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I thing you have not understood the requirment of writing this query 

Given the sparcity of the information you provide, and your unwillingness to answer questions, are you suprised?

Behold, once you actually engage with us and provide information and feedback, your problem gets solved:
with 
  data as (
    select MWC_ESMM_SEM_SMST_SECURITY_ID
          ,MWC_EXCH_ID
          ,MWC_BUY_PRICE,
           row_number () over (partition by MWC_ESMM_SEM_SMST_SECURITY_ID 
                               order by case when mwc_exch_id = 'N' and mwc_buy_price > 0 then 2 
                                             when mwc_exch_id = 'B' then 1 
                                             else 0 end desc) rn
    from simple_test
    ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID,mwc_exch_id DESC
  )
select  MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EXCH_ID, MWC_BUY_PRICE
from data
where rn = 1;

MWC_ESMM_SEM_SMST_SECURITY_ID  MWC_EXCH_ID MWC_BUY_PRICE          
------------------------------ ----------- ---------------------- 
ACCLTDEQNR                     N           1150.55                
HDFBANEQNR                     B           1097.25                
ICIBANEQNR                     B           834.2                  
LARTOUEQNR                     B           2451                   
TCSLTDEQNR                     N           1001.7        
Re: split data of one column on bases of another column (multi merge ) [message #271390 is a reply to message #271380] Mon, 01 October 2007 08:57 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
JRowbottom,

Thanks for providing the insert script.

Why do you need to have a case statement when you can add a where condition in the subselect. Your thoughts are welcome.

  1  with
  2    data as (
  3      select MWC_ESMM_SEM_SMST_SECURITY_ID
  4            ,MWC_EXCH_ID
  5            ,MWC_BUY_PRICE,
  6             row_number () over (partition by MWC_ESMM_SEM_SMST_SECURITY_ID order by MWC_EXCH_ID desc) rn
  7      from simple_test
  8      where mwc_buy_price > 0
  9      ORDER BY MWC_ESMM_SEM_SMST_SECURITY_ID,mwc_exch_id DESC
 10    )
 11  select  MWC_ESMM_SEM_SMST_SECURITY_ID, MWC_EXCH_ID, MWC_BUY_PRICE
 12  from data
 13* where rn = 1
SQL> /

MWC_ESMM_SEM_SMST_SECURITY_ID  M MWC_BUY_PRICE
------------------------------ - -------------
ACCLTDEQNR                     N       1150.55
HDFBANEQNR                     B       1097.25
ICIBANEQNR                     B         834.2
LARTOUEQNR                     B          2451
TCSLTDEQNR                     N        1001.7



Ofcourse, same query is posted by dhanjay in this thread.

Regards

Raj

P.S : Typo corrected.

[Updated on: Mon, 01 October 2007 08:58]

Report message to a moderator

Re: split data of one column on bases of another column (multi merge ) [message #271395 is a reply to message #271390] Mon, 01 October 2007 09:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I was in a hurry, and the Case statement involved the least thought - pure lazyness really.

I saw Dhanjays solution, but the fact that the OP kept posting made me assume that there was a problem with it that he (the op) wasn't willing to tell us about.
Re: split data of one column on bases of another column (multi merge ) [message #271810 is a reply to message #271395] Wed, 03 October 2007 01:12 Go to previous message
sonalshastry
Messages: 52
Registered: September 2007
Member
Hi all

Thank you for your help and for being kind to me,
i am sorry for that confusion raise by me in privious replies

Thanks a lot

Best Regards
Sonal
Previous Topic: Parsing CSV file into external tables
Next Topic: CALLING STORED PROCEDURE
Goto Forum:
  


Current Time: Wed Dec 07 22:11:41 CST 2016

Total time taken to generate the page: 0.08945 seconds