| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL / PLSQL help in converting multiple rows into columns
Pardon My Haste
I am using webmail to access my email. The link did not show up properly on the page. Missed it.
I got exactly the way it was needed.
Thanks a ton
Regards
Shiva
-----Original Message-----
To: Baswannappa, Shiva; 'Multiple recipients of list ORACLE-L '
Sent: 5/8/03 1:19 PM
It will be good idea to read the email completely ... it tells you where to get the code from.
Raj
-----Original Message-----
<mailto:SXBaswan_at_dcss.com> ]
Sent: Thursday, May 08, 2003 2:14 PM
To: Jamadagni, Rajendra; 'Multiple recipients of list ORACLE-L '
Thanks Raj
It does not seem to work for me. I get "ORA-00904: "STRAGG": invalid identifier"
Any clue?
Regards
Shiva
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 5/8/03 1:31 PM
You mean like this ???
CREATE TABLE att (att_name VARCHAR2(30), att_value VARCHAR2(30),
site_code VARCHAR(30), material_code VARCHAR2(30))
/
INSERT INTO att VALUES ('vendor_name','abc','A','m1')
/
INSERT INTO att VALUES ('vendor_number','xyz','A','m1')
/
INSERT INTO att VALUES ('vendor_name','def','B','m2')
/
INSERT INTO att VALUES ('vendor_number','ghi','B','m2')
/
SELECT col_1, stragg(col_2), stragg(col_3), col_4
FROM(
SELECT site_code col_1,
CASE WHEN att_Name = 'vendor_name' THEN att_value ELSE NULL END col_2,
CASE WHEN att_Name = 'vendor_number' THEN att_value ELSE NULL
END col_3,
material_code col_4
FROM att)
GROUP BY col_1, col_4
/
col1 col2 col3 col4
---- ---- ---- -----
A abc xyz m1 B def ghi m2
for stragg code head to
http://asktom.oracle.com/pls/ask/f?p=4950:8:144926727866403634::NO::F495
<http://asktom.oracle.com/pls/ask/f?p=4950:8:144926727866403634::NO::F49
5>
0_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402
<
http://asktom.oracle.com/pls/ask/f?p=4950:8:144926727866403634::NO::F49
<http://asktom.oracle.com/pls/ask/f?p=4950:8:144926727866403634::NO::F49
>
50_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402> ,
Raj
-----Original Message-----
<mailto:SXBaswan_at_dcss.com>
< mailto:SXBaswan_at_dcss.com <mailto:SXBaswan_at_dcss.com> > ]
Sent: Thursday, May 08, 2003 1:27 PM
To: Multiple recipients of list ORACLE-L
Hi Every Guru
I rememeber to have seen this request in the list few months ago.
I have following to achieve
Table "vendor" has following columns:
site_code (not null) vendor_name (not null) vendor_number (not null) material_code (not null)
Table "ATT" has following columns:
att_name
att_value
site_code
values stored in ATT table are
att_name att_value site_code material_code -------- --------- ---------- ------------- vendor_name abc A m1 vednor_number xyz A m1 vendor_name def B m2 vendor_number ghi B m2
I need to insert values from ATT table into vendor table in one go per
record. To do that, I need to convert ATT table multirows into single
row
for each of site code. Like
A, abc, xyz, m1
B, def, ghi, m2
Long shot thoughts are to use plsql tables, varrays manipulate and then insert/update the vendor table.
I may be able to convince the client to let me create additional table.
The
client does not want to or like to do that, as that involves whole
process
of getting approvals, validations etc.
The DB is oracle 9i ver 2. on HP-UX11
I appreciate "Any" thoughts leading to solution.
Regards
Shiva
-- Please see the official ORACLE-L FAQ: http://www.orafaq.netReceived on Thu May 08 2003 - 15:14:47 CDT
<http://www.orafaq.net>
< http://www.orafaq.net <http://www.orafaq.net> >
-- Author: Baswannappa, Shiva INET: SXBaswan_at_dcss.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com>
< http://www.fatcity.com <http://www.fatcity.com> >
San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). <<ESPN_Disclaimer.txt>> <<ESPN_Disclaimer.txt>> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Baswannappa, Shiva INET: SXBaswan_at_dcss.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |