Re: Denormalizing a database table

Thx Andy,

Adding a lil bit more complexity

My Source Table1 has
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
4     SCM1    name    DFW
4     SCM1    OVal   300
4     SCM1    OSum   300

My Source Table 2 has

SNum Date

1         01/01/2006
2         01/01/2006
3         01/01/2006
4         02/01/2006

Desired Output
DFW 300 600 01/01/2006
ORL 100 200 01/01/2006
DFW 300 300 02/01/2006 In other words, denormalizing the data and also aggregating the data in source table1 using data from source table2

Hope it is not confusing.

Thx
Alk

Andy Hassall wrote:
> 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
Received on Wed Aug 09 2006 - 14:51:42 CDT