Crosstab query

From: Gary Townsend <gary_at_spatialmapping.com>
Date: Thu, 18 Oct 2001 21:14:41 GMT
Message-ID: <3BCF4756.90706_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 Thu Oct 18 2001 - 23:14:41 CEST