Oracle-L: RE: SQL / PLSQL help in conerting multiple rows into columns

From: Chelur, Jayadas {PBSG} <jayadas.chelur_at_pepsi.com>
Date: Thu, 08 May 2003 11:06:34 -0800
Message-ID: <F001.005943D4.20030508110634@fatcity.com>

Try this ...

INSERT INTO vendor
SELECT site_code,

MAX(DECODE(att_name,'vendor_name', att_value,NULL)) AS vendor_name,

MAX(DECODE(att_name,'vendor_number',att_value,NULL)) AS vendor_number,

	   MAX(material_code) AS material_code
FROM	   ATT

GROUP BY site_code;

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

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

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Chelur, Jayadas {PBSG}
INET: jayadas.chelur_at_pepsi.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------