Re: SQL question

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Thu, 20 Apr 2006 09:26:47 GMT
Message-ID: <rtI1g.11117$vy1.10102@news-server.bigpond.net.au>

This is an example of a table-pivot query. Search asktom.oracle.com for an explanation.

Try:

SELECT

      s_id,
      name,
      notes,
      MAX(DECODE(property_id,1,property_value)) AS property_1,
      MAX(DECODE(property_id,2,property_value)) AS property_2
   FROM
      first_table
   INNER JOIN
      second_table

   USING (s_id)
/

This works in 9i+ for the 'using' clause.

Douglas

<kurt-erich.finger_at_arcor.de> wrote in message news:1145519210.498936.86700_at_g10g2000cwb.googlegroups.com...
>I have a problem "joining" two tables.
> One table contains data like S_ID, name, notes, created_date.
> A second table S_ID, property_ID, property_value
> For property_ID values from 1 to 10 are possible the meaning of the
> property_idis stored in a separate table (property_id, name).
> I would like to create an output in the form:
> S_ID, name, notes, created_date, property_id=1, property_id=2
>
> I don't need a dynamic table, the table should consist of fields from
> table1 plus two properties.
> Can this be done with a single sql statement without first retrieving
> the records from table1 and then looping through this table to get the
> properties from table2?
>
Received on Thu Apr 20 2006 - 04:26:47 CDT