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

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 08 May 2003 10:31:59 -0800
Message-ID: <F001.00594215.20030508103159@fatcity.com>

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::F4950_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-----
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: Jamadagni, Rajendra INET: Rajendra.Jamadagni_at_espn.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).

text/plain attachment: ESPN_Disclaimer.txt

Received on Thu May 08 2003 - 13:31:59 CDT