Need to query data as if rows were columns

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. Received on Fri Oct 22 1999 - 17:01:10 CDT