Re: sql - denormalising ?

From: Alexander Ignatyev <align_at_b52tech.com>
Date: Wed, 21 Mar 2001 22:42:37 +0300
Message-ID: <99b035$gs6$1@gavrilo.mtu.ru>

To do this :

1. you can add new column in your table - type of attribute

 id attribute attr_type

---

 1 yellow color

 1    rough     surface
 2    blue      color

 2 smooth surface

and use "group by" clause :

select
  id,
  max(decode(attr_type,'surface' ,attribute,null)) surface,   max(decode(attr_type,'color' ,attribute,null)) color from <table_name>
group by id

or

2) If table contains exactly 2 rows for each id and column order (color,surface) or (surface,color) is insignificant you can use:

select
  id,
  min(attiibute) surface_or_color,
  max(attiibute) color_or_surface
from <table_name>
group by id

HTH. "Carsten Jacobs" <carsten.jacobs_at_web.de> wrote :
> Hi,
>
> I have a table with multiple entries (2) for one entity like
>
> id attribute
> ---------------
> 1 yellow
> 1 rough
> 2 blue
> 2 smooth
>
> How can I get a result like
> 1 yellow rough
> 2 blue smooth
>
> Do I need a procedure for that or can I do it with a single sql statement?
Received on Wed Mar 21 2001 - 13:42:37 CST