| merging of rows based on column value [message #425149] |
Wed, 07 October 2009 15:38  |
sekharsomu
Messages: 72
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 #425234 is a reply to message #425149] |
Thu, 08 October 2009 03:35  |
sekharsomu
Messages: 72
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 |
sekharsomu
Messages: 72
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 #425958 is a reply to message #425249] |
Tue, 13 October 2009 07:01 |
sekharsomu
Messages: 72
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
