RE: Query Help

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Mon, 2 Oct 2000 17:09:45 -0400
Message-Id: <10637.118345@fatcity.com>

Actually, in your case there is. Since you know the exact number of elements that you wish to return (3), you can do the following:

col prop_value format A20
select p1.user_id, p1.prop_value, p2.prop_value, p3.prop_value from user_prop p1, user_prop p2, user_prop p3 where p1.user_id = p2.user_id

and p2.user_id = p3.user_id
and p1.prop_id = 'BDATE'
and p2.prop_id = 'SEX'
and p3.prop_id = 'CITY'

which returns:

USER_ID PROP_VALUE PROP_VALUE PROP_VALUE

--------- -------------------- -------------------- --------------------
     1234 18051999             Male                 Toronto

If you don't know for sure that each element exists for each user, you'll have to use outer joins, as follows:

select p1.user_id, p1.prop_value, p2.prop_value, p3.prop_value from user_prop p1, user_prop p2, user_prop p3 where p1.user_id = p2.user_id (+)

and p1.user_id = p3.user_id (+)
and p1.prop_id = 'BDATE'
and p2.prop_id(+) = 'SEX'
and p3.prop_id(+) = 'CITY'

union
select p1.user_id, p1.prop_value, p2.prop_value, p3.prop_value from user_prop p1, user_prop p2, user_prop p3 where p2.user_id = p1.user_id (+)

and p2.user_id = p3.user_id (+)
and p1.prop_id(+) = 'BDATE'
and p2.prop_id = 'SEX'
and p3.prop_id(+) = 'CITY'

union
select p1.user_id, p1.prop_value, p2.prop_value, p3.prop_value from user_prop p1, user_prop p2, user_prop p3 where p3.user_id = p1.user_id (+)

and p3.user_id = p2.user_id (+)
and p1.prop_id(+) = 'BDATE'
and p2.prop_id(+) = 'SEX'
and p3.prop_id = 'CITY'

which returns (in my test data):
USER_ID PROP_VALUE PROP_VALUE PROP_VALUE

--------- -------------------- -------------------- --------------------
     1111 01011970
     1234 18051999             Male                 Toronto
                                                    Cleveland

It gets a bit crazy, though, and my personal preference would be to create a SQL function that returns a string -- but then you don't have the nice columns...

HTH, Diana

-----Original Message-----
From: Ashish Shah [mailto:ar_shah_at_yahoo.com] Sent: Monday, October 02, 2000 3:36 PM
To: Multiple recipients of list ORACLE-L Subject: Query Help

Hi All,

I have a table called
user_prop
(user_id ,prop_id,prop_value)

When I do select user_id,prop_value from user_prop where prop_id in ('BDATE','SEX','CITY');

It would display like..

1234 18051999
1234 Male
1234 Toronto

Is there a way to display result like..

1234 18051999 Male Toronto --in one row..!!

Thanks.
Is there a way to display query results

Ashish
Toronto, Canada

Do You Yahoo!?
Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free! http://photos.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ashish Shah
  INET: ar_shah_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Mon Oct 02 2000 - 16:09:45 CDT