Re: Denormalizing a database table

On 9 Aug 2006 12:09:21 -0700, alkkmrz2004_at_yahoo.com wrote:

>Any help with creating a SP that denormalizes the below table to the
>desired output will be appreciated.
>
>Current Input Table Structure:
>
>SNum KeyName KeyVal value
>1 SCM1 name DFW
>1 SCM1 OVal 200
>1 SCM1 OSum 500
>2 SCM2 name ORL
>2 SCM2 OVal 100
>2 SCM2 OSum 200
>3 SCM1 name DFW
>3 SCM1 OVal 100
>3 SCM1 OSum 100

 Uh-oh, looks like an EAV design...

>Desired O/p in a secondary temp table
>
>DFW 200 500
>ORL 100 200
>DFW 100 100
SQL> select t1.value, t2.value, t3.value   2 from t t1
  3 join t t2 on (t1.snum = t2.snum)
  4 join t t3 on (t1.snum = t3.snum)

  5  where  t1.keyval = 'name'
  6  and    t2.keyval = 'OVal'
  7  and    t3.keyval = 'OSum';

VALUE VALUE VALUE
-------- -------- --------

DFW      200      500
ORL      100      200
DFW      100      100

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool