Re: Cross Tab Query

Must be something like this:

select PS1.POLYGONID as "PolygonID", PS2.VALUE as "Polygon_CC", PS1.VALUE as "Comment"

    from PS PS1, PS PS2

        where PS1.POLYGONID = PS2.POLYGONID;

hth
Andy

"Gary Townsend" <gary_at_spatialmapping.com> schrieb im Newsbeitrag news:3BCF42C1.90807_at_spatialmapping.com...
> I know this has been asked more than once and i apologize in advance for
> anyone who feels like they need to flame me, (which i'll ignore
> anyways;)). However i have a highly normalized database with text data
> mostly which i need to string out into a less nromalized form so that
> the users can understand it better.
>
> Sample table
>
> Table: PS
> POLYGONID MEASUREID VALUE
> 101 111 'This polygon is irregular'
> 102 111 'This polygon is blue'
> 101 112 0.5
> 102 112 0.3
>
> TABLE: Measure
> MEASUREID MEASURENAME
> 111 'POLYGON_COMMENTS'
> 112 'POLYGON_CC'
>
>
> I need to display them like this
>
> POLYGONID POLYGON_CC COMMENTS
> 101 0.5 'This polygon is irregular'
> 102 0.3 'This polygon is blue'
>
>
> SO far using the following
>
> SELECT POLYGONID,
> DECODE(MEASURE,'POLYGON_CC',VALUE) "POLYGON_CC",
> DECODE(MEASURE,'POLYGON_COMMENTS',VALUE) "COMMENTS"
> FROM sample a,tblMEASURES b
> WHERE a.MEASUREID = b.MEASUREID AND (MEASURE='POLYGON_CC' or
> MEASURE='POLYGON_COMMENTS')
>
>
> I get
>
> POLYGONID POLYGON_CC COMMENTS
> 101 0.5
> 102 0.3
> 101 'This polygon is irregular'
> 102 'This polygon is blue'
>
> Thanks for any help
>
> Gary Townsend
> Internet Services Administrator / Database Developer
> Spatial Mapping Ltd.
> http://www.spatialmapping.com
> p: 250 564 1928
> f: 250 564 0751
> e: gary_at_spatialmapping.com
>
Received on Fri Oct 19 2001 - 06:20:17 CDT