Re: Need to query data as if rows were columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 23 Oct 1999 09:27:01 -0400
Message-ID: <XrcROG1Eg7vdxKwcqzklSbahIMTD@4ax.com>

A copy of this was sent to daisyt_at_my-deja.com (if that email address didn't require changing) On Fri, 22 Oct 1999 22:01:10 GMT, you 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.
>

select customer,

       max( decode( field_type, 'firstname', data, null ) ) FirstName,
       max( decode( field_type, 'lastname',  data, null ) ) LastName,
       max( decode( field_type, 'state',     data, null ) ) state

from T
group by customer;

will do it for you.

>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.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Oct 23 1999 - 08:27:01 CDT