RE: SQL / PLSQL help in converting multiple rows into columns

From: Baswannappa, Shiva <SXBaswan_at_dcss.com>
Date: Thu, 08 May 2003 11:17:54 -0800
Message-ID: <F001.00594484.20030508111754@fatcity.com>

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 0_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402 <http://asktom.oracle.com/pls/ask/f?p=4950:8:144926727866403634::NO::F49 50_P8_DISPLAYID,F4950_P8_CRITERIA:2196162600402> , Raj

---

---

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
<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.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>  
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>> 
-- 
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).

Received on Thu May 08 2003 - 14:17:54 CDT