Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Transform Columns to Rows (Unusual)

Re: Transform Columns to Rows (Unusual)

From: SoulSurvivor <markyg_7_at_yahoo.co.uk>
Date: 6 Feb 2003 02:03:23 -0800
Message-ID: <8d9c6fd.0302060203.4da1e976@posting.google.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US