Re: Need to query data as if rows were columns

A copy of this was sent to "-=< a q u a b u b b l e >=-" <aquabubble_at_Remove.No.Parma.Ham.Remove.geocities.com> (if that email address didn't require changing) On Sun, 24 Oct 1999 23:57:44 +0100, you wrote:

>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:XrcROG1Eg7vdxKwcqzklSbahIMTD_at_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.
>
>That's excellent! I'm right in thinking that max is used so that you can
>group by?...
>

Yes, if we left off the group by we would get:

C1 mary (null) (null)
C1 (null) wilson (null)
C1 (null) (null) FL

The group by 'squashes' down this matrix into 1 row that is:

C1 mary wilson FL

--
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 Sun Oct 24 1999 - 19:49:28 CDT