Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Transform Columns to Rows (Unusual)
Ok,
I initially replied in the other newsgroup C.D.O. However, i have now wangled a SQL*Plus session and since i have a *very* quiet moment, rose to the challenge of attempting this.
I made sample data based on your scenario. The PL/SQL creates a new table mg_new_table to match your required output.
You should be able to cut and paste the following in a SQL*Plus session.
Hope it helps! Mines a pint! :-)
CREATE TABLE mg_old_table
(id number,
productname varchar2(20),
oldtype varchar2(2000));
insert into mg_old_table values (1, 'Everclear', 'A'); insert into mg_old_table values (2, 'Doritos', 'B,C,D,'); insert into mg_old_table values (3, 'Snickers', 'E,F,'); insert into mg_old_table values (4, 'Dr Pepper', 'G,H,I,J,'); insert into mg_old_table values (5, 'Bounty', NULL);
CREATE TABLE mg_new_table
(id number,
productname varchar2(20),
newtype varchar2(20));
DECLARE
CURSOR c_old IS
SELECT * FROM mg_old_table;
v_new_field VARCHAR2(20); v_num_fields NUMBER; v_start NUMBER; v_end NUMBER; v_null_field BOOLEAN := FALSE;
BEGIN FOR rec IN c_old LOOP
v_num_fields := 0; v_null_field := FALSE; -- -- The following assumes a string structure of 'A'B'C,' (with last comma) -- v_num_fields := LENGTH(rec.oldtype) -LENGTH(REPLACE(rec.oldtype, ',', ''));
IF v_num_fields = 0 AND rec.oldtype IS NOT NULL THEN v_num_fields := 1; END IF; IF rec.oldtype IS NULL THEN v_null_field := TRUE; END IF; -- If field is not null, insert values into new table IF NOT v_null_field THEN FOR x IN 1..v_num_fields LOOP v_start := INSTR(rec.oldtype, ',', 1, x) -1; v_end := INSTR(rec.oldtype, ',', 1, x); v_new_field := substr(rec.oldtype, v_start, v_end -v_start);
INSERT INTO mg_new_table VALUES (rec.id, rec.productname, v_new_field); END LOOP; ELSE -- Insert NULL for last field INSERT INTO mg_new_table VALUES (rec.id, rec.productname, NULL); END IF;
END LOOP; COMMIT; END;
Output
SQL> l
1 select * from mg_new_table
2* order by id
SQL> /
ID PRODUCTNAME NEWTYPE
---------- -------------------- -------------------- 1 Everclear A 2 Doritos B 2 Doritos C 2 Doritos D 3 Snickers E 3 Snickers F 4 Dr Pepper G 4 Dr Pepper H 4 Dr Pepper I 4 Dr Pepper J 5 Bounty
11 rows selected.
M
dbergquist_at_bd.com (David Berg) wrote in message news:<1274998b.0302051143.40dfb5bd_at_posting.google.com>...
> Have a problem similar to the typical transforming columns into row > that i have seen posted here, but with some unique twists that making > it a problem for me. > > Need query that transforms an initial dataset and adds a new row for > each string initially stored as a comma delimited array in varchar > field rather than a column for each entry to transform. Return 1 row > if 0 or 1 string in array field. > > Source: Table1 > > ID ProductName OldType > ------------------------------ > 1 Everclear textstring1 > 2 Doritos textstring1,textstring2, > 3 Snickers textstring1,textstring2....textstring_N, > 4 Dr Pepper (null) > > -ID is row PriKey > -ProductName is field in table. > -OldType is varchar array field to transform and create new record set > based on. It contains comma delimited text of 0 to 10 strings (last > entry will be marked with comma if there is one or more string in > list) > > > Output: View1 or StoredProc1 > > ID ProductName ExpandedType > -------------------------------- > 1 Everclear textstring1 > 2 Doritos textstring1 > 2 Doritos textstring2 > 3 Snickers textstring1 > 3 Snickers textstring2 > ... ... > 3 Snickers textstringN > 4 Dr. Pepper (null) > > Thanks for your help.Received on Thu Feb 06 2003 - 04:03:23 CST