Re: Turning column into row

On Wed, 22 May 2002, Tille, Andreas wrote:

> On Wed, 22 May 2002, Wm. G. Urquhart wrote:
> > Depending on how many properties you had you could include these in the
> > first table and remove the other two table. Using a hash value pair or
> > sommit similar (Value=Desc, Value=Desc) you could then do bitwise
> > operations on the column to extract the information you need.
> Well, there are not much but how to insert a new Property with this
> approach. Quite impossible.
>
> Anyway, thanks for the thought
>
> Andreas.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

Not at all if you have for example 6 properties:

Prop 1 = 1 ;
Prop 2 = 2 ;
Prop 3 = 4 ;
Prop 4 = 8 ;
Prop 5 = 16 ;
Prop 6 = 32 ;

The user will select the properties applicable to the Item; so for this
example the new item has Properties 1, 3 and 6. These property values are
added to give a total of 37. This is the value stored in the table column.

Once you have the rows you can then && the Properties column to see if a
property is set. I know this works as I've done it before in Oracle,
Oracle even supports bitwise predicates. But I'm not sure if PostgreSQL
does.

if (Item.Column && Prop1)
Do this ;
else
Property Not supported... ;
end if ;

HTH

--
Regards,

Wm. G. Urquhart
Custom Software Solutions
http://www.wurquhart.co.uk