Re: Anti-pivot

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Mon, 21 Apr 2008 10:23:51 -0700 (PDT)
Message-ID: <e7ee4856-69f4-44d0-b9f0-629d74b3826d@l64g2000hse.googlegroups.com>

On Apr 21, 10:18 am, "rogergor..._at_gmail.com" <rogergor..._at_gmail.com> wrote:
> On Apr 21, 7:44 am, Mountain..._at_gmail.com wrote:
>
>
>
>
>
> > Hello,
>
> > I have a table which has 3 columns: user id, key and value. A user may
> > only have one instance of a given key. However, the key may have
> > multiple values; these are held as comma-separated values within the
> > value column (there can be any number of these values in the row). An
> > example:
>
> > User_id  Key     Value
> > -------  ------  -----
> > 1        MY_KEY  1
> > 2        MY_KEY  1,2,3
>
> > Firstly: yes, I know this is *extremely* hideous database design; I
> > cringed when I saw it. Unfortunately, the design is set in stone and I
> > not able to change it.
>
> > The question is, is there any way of getting out the values with one
> > row
> > for each in SQL*Plus? So, for example, getting the values for key
> > MY_KEY
> > for user_id 2 would produce:
>
> > User_id  Key     Value
> > -------  ------  -----
> > 2        MY_KEY  1
> > 2        MY_KEY  2
> > 2        MY_KEY  3
>
> > I've had a quick look at pivoting queries but they seem to be doing
> > the
> > opposite of what I'm doing here.
>
> > Version: 10.2.0.1.0
>
> > Many thanks,
>
> > Oaf
>
> You might want to look into REGEXP in Oracle 10G, or if that's too
> uncomfortable, create a view, using some Pl/SQL to populate the table.
>
> Either way, that datamodel is going to be a huge pain in the *** to
> work with and will only cause more problems as it won't scale.
>
> Roger Gorden- Hide quoted text -
>
> - Show quoted text -

Well here is one possible technique to extract the data from a column. Warning I have tested beyound this simple query.

UT1 > drop table t;

Table dropped.

UT1 > create table t (fld1 number, fld2 varchar2(10), fld3 varchar2(20) );

Table created.

UT1 > insert into t values (1,'one','1');

1 row created.

UT1 > insert into t values (2,'one','1,2,3,4');

1 row created.

UT1 > insert into t values (3,'two','1,2');

1 row created.

UT1 > insert into t values (4,'one','11,21,313,414');

1 row created.

UT1 > col fld2  format a4
UT1 > col fld3  format a13
UT1 > col fld4  format a4
UT1 > col fld5  format a4
UT1 > col fld6  format a4
UT1 > col fld7  format a4
UT1 > select fld1, fld2, fld3,
  2         case when instr(fld3,',',1,1) > 0
  3              then substr(fld3,1,instr(fld3,',',1) - 1)
  4              else fld3
  5         end as fld4,
  6         case when instr(fld3,',',1,1) > 0
  7              then substr(fld3,instr(fld3,',',1,1) + 1,
  8                   case when instr(fld3,',',1,2) > 0
  9                        then instr(fld3,',',1,2) - 1 -
instr(fld3,',',1,1)
 10                        else length(fld3)
 11                   end )
 12              else NULL
 13              end as fld5,
 14         case when instr(fld3,',',1,2) > 0
 15              then substr(fld3,instr(fld3,',',1,2) + 1,
 16                   case when instr(fld3,',',1,3) > 0
 17                        then length(fld3) - instr(fld3,',',1,3)
 18                        else length(fld3)
 19                   end )
 20              else NULL
 21              end as fld6,
 22          case when instr(fld3,',',1,3) > 0
 23               then substr(fld3,instr(fld3,',',1,3) +
1,length(fld3))
 24               else NULL
 25               end as fld7

 26 from t
 27 order by fld1, fld2
 28 /

      FLD1 FLD2 FLD3 FLD4 FLD5 FLD6 FLD7 ---------- ---- ------------- ---- ---- ---- ----

         1 one  1             1
         2 one  1,2,3,4       1    2    3    4
         3 two  1,2           1    2
         4 one  11,21,313,414 11   21   313  414

The MERGE statement can be used to unpivot data. With 11g Oracle provides a pivot and unpivot statement.

HTH -- Mark D Powell -- Received on Mon Apr 21 2008 - 12:23:51 CDT