Re: Need to query data as if rows were columns

Hi.

 Try :

 SELECT t1.customer, t1.lastname, t2.firstname, t3.state  FROM
   table t1,
   table t2,
   table t3
 WHERE
   t1.field_type = 'lastname'

   AND t2.field_type = 'firstname'
   AND t2.customer(+) = t1.customer
   AND t3.field_type = 'state'
   AND t3.customer(+) = t1.customer;

 In order to get decent performance
 define an indexes on field_type and customer fields.  The query assumes that lastname always exists.

 HTH. Michael.

In article <7uqmr4$j0c$1_at_nnrp1.deja.com>,   daisyt_at_my-deja.com wrote:
> I am stuck!
> I need to query a table and select as if rows meeting similar criteria
> were actually columns in the table.
>
> Simplified example:
>
> Field_type Data Customer
> firstname mary C1
> firstname kate C2
> firstname jane C3
> firstname sally C4
> lastname jones C2
> lastname smith C3
> lastname wilson C1
> lastname woody C4
> state GA C4
> state FL C1
>
> and so on......
>
> I need to write a query to show a distinct list of customer ids by
> distinct field type, i.e.
>
> Customer firstname lastname state
> C1 mary wilson GA
> C2 kate jones
> C3 jane smith
> C4 sally woody FL
>
> If I use:
>
> select data, data, data, data
> from table
> where field_type='firstname'
> and field_type='lastname'
> and field_type='state'
>
> I obviously get NO records because in the table each row has only one
> field type.
>
> If I use "or" instead of "and" between each field type I get something
> like:
>
> customer data data data
> C1 mary
> C1 wilson
> C1 GA
> C2 kate
> C2 jones
> C3 jane
> C3 smith
> C4 sally
> C4 woody
> C4 FL
>
> But, I need a unique row for each customer.
>
> Basically, I need to convert rows to columns to pull my query. This
> data gets updated real time, so I can't just make one new table and
run
> it once. This will have to be run on an ongoing basis.
>
> Thanks for your help and ideas!!!!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 18:18:17 CDT