Home » SQL & PL/SQL » SQL & PL/SQL » merging of rows based on column value (9i)
merging of rows based on column value [message #425149] Wed, 07 October 2009 15:38 Go to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Here are my table create and insert script:

 CREATE TABLE "T_SAMP" 
   (	"SNO" NUMBER, 
	"ITEM_NAME" VARCHAR2(4000 BYTE), 
	"VALUE" VARCHAR2(4000 BYTE)
   )


INSERT INTO "T_SAMP" (SNO, ITEM_NAME, VALUE) VALUES ('1', 'ship', '123')
INSERT INTO "T_SAMP" (SNO, ITEM_NAME, VALUE) VALUES ('1', 'mag', '4567')
INSERT INTO "T_SAMP" (SNO, ITEM_NAME, VALUE) VALUES ('1', 'fire', '5677')
INSERT INTO "T_SAMP" (SNO, ITEM_NAME, VALUE) VALUES ('2', 'ship', '5656')
INSERT INTO "T_SAMP" (SNO, ITEM_NAME, VALUE) VALUES ('2', 'mag', '657657')
INSERT INTO "T_SAMP" (SNO, ITEM_NAME, VALUE) VALUES ('2', 'fire', '346457')


after that my table looks like this

sno    item_name value
1	ship	123
1	mag	4567
1	fire	5677
2	ship	5656
2	mag	657657
2	fire	346457


now i need to merge the tables with common serial no and data should be reflected in new columns based item_name say if its ship the column name should be v_ship similarly mag should be v_mag
fire should be v_fire

my end result should look like this

sno  v_ship  v_mag v_fire
1     123    4567   5677
2    5656   657657  346457



i have tried the following proc please correct this:

create or replace procedure tester  is
cursor c1 is select item_name,value from t_samp;
vitm t_samp.item_name%type;
vno number(8);
v_ship number(8); 
v_mag number(8);
v_fire number(8);
begin
open c1;
loop
fetch c1 into  vitm,vno;
exit when c1%notfound;
if vitm='ship'  then
v_ship:= vno;
elsif vitm='mag'  then
v_mag:=vno;
elsif vitm='fire'  then
v_fire:=vno;
end if;
end loop;
commit;
close c1; 
<i think here comes an sql statement something ..but i didnt get how>
end;
/


please assist me here
thanks

[Updated on: Wed, 07 October 2009 16:02]

Report message to a moderator

Re: merging of rows based on column value [message #425155 is a reply to message #425149] Wed, 07 October 2009 17:14 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
SELECT SNO,case WHEN ITEM_NAME='ship' then EVALUES   END V_SHIP,
case WHEN ITEM_NAME='mag' then EVALUES   END V_mag,
case WHEN ITEM_NAME='fire' then EVALUES   END v_fire
from T_SAMP


i have also tried this everything is okay here but the serial no repeat can i restrict it to one row

[Updated on: Wed, 07 October 2009 17:15]

Report message to a moderator

Re: merging of rows based on column value [message #425164 is a reply to message #425149] Wed, 07 October 2009 21:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
if you want only one row then put a where clause on it to filter your rows correctly. You will first ofcourse have to describe to yourself what the logic is for selecting the rows you want. It seems you do not have that yet.

Good luck, Kevin
Re: merging of rows based on column value [message #425181 is a reply to message #425155] Wed, 07 October 2009 23:24 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i have also tried this everything is okay here but the serial no repeat can i restrict it to one row

Use MAX/GROUP BY sno

Regards
Michel
Re: merging of rows based on column value [message #425234 is a reply to message #425149] Thu, 08 October 2009 03:35 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Thanks for the suggestion I, have actually tried them but it return me an error:
SELECT max(SNO),case WHEN ITEM_NAME='ship' then EVALUES   END v_ship,
case WHEN ITEM_NAME='mag' then EVALUES   END v_mag,
case WHEN ITEM_NAME='fire' then EVALUES   END v_fire
from T_SAMP
group by v_ship,v_mag,v_fire


Here is the error in SQL DEVELOPER:

Error starting at line 1 in command:
SELECT max(SNO),case WHEN ITEM_NAME='ship' then EVALUES   END v_ship,
case WHEN ITEM_NAME='mag' then EVALUES   END v_mag,
case WHEN ITEM_NAME='fire' then EVALUES   END v_fire
from T_SAMP
group by v_ship,v_mag,v_fire
Error at Command Line:5 Column:22
Error report:
SQL Error: ORA-00904: "V_FIRE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:


Please guide me with thing...
Re: merging of rows based on column value [message #425237 is a reply to message #425234] Thu, 08 October 2009 03:44 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Thanks guys,

U guys are really helpful in the time of need i have got the way to solve my problem from another source. i dnt know whether its allowed to post answer received from external source here. So, i am not publishing it. if its ok then i will put it here.Anyways thanks for all the support i am really struggling with this
query and now its done
Re: merging of rows based on column value [message #425248 is a reply to message #425234] Thu, 08 October 2009 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't say "MAX(sno)" but "MAX(other stuff) GROUP BY sno".

Regards
Michel
Re: merging of rows based on column value [message #425249 is a reply to message #425237] Thu, 08 October 2009 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sekharsomu wrote on Thu, 08 October 2009 10:44
...i dnt know whether its allowed to post answer received from external source here...

Yes you can post it, more you should (as stated in forum guide).

Regards
Michel

Re: merging of rows based on column value [message #425958 is a reply to message #425249] Tue, 13 October 2009 07:01 Go to previous messageGo to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
Sorry,
for a too late post
but here is the query...


SELECT   sno, SUM (CASE
                      WHEN item_name = 'ship'
                         THEN EVALUES
                      ELSE '0'
                   END) v_ship,
         SUM (CASE
                 WHEN item_name = 'mag'
                    THEN EVALUES
                 ELSE '0'
              END) v_mag,
         SUM (CASE
                 WHEN item_name = 'fire'
                    THEN EVALUES
                 ELSE '0'
              END) v_fire
    FROM t_samp
GROUP BY sno


Micheal,i know this is same as your suggestion and i am always thankful for your valuable time and suggestions.Just used SUM instead of MAX
Re: merging of rows based on column value [message #425960 is a reply to message #425958] Tue, 13 October 2009 07:19 Go to previous message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't be sorry, thanks for the feedback.

Regards
Michel
Previous Topic: Job scheduling
Next Topic: Issue with multiple column Sub-Query
Goto Forum:
  


Current Time: Mon Sep 26 16:18:20 CDT 2016

Total time taken to generate the page: 0.18789 seconds