| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query Help
Hi,
At 02:15 PM 10/2/00 -0800, you wrote:
>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
You might get better results (meaning more efficient query) with something like this:
select user_id, max(decode(prop_id,'BDATE',prop_value,null)) BDATE,
max(decode(prop_id,'SEX',prop_value,null)) SEX,
max(decode(prop_id,'CITY',prop_value,null)) CITY
group by user_id;
The max is not strictly necessary for its function, but you do need some sort of group function and I find max to be convenient.
>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-----
>Sent: Monday, October 02, 2000 3:36 PM
>To: Multiple recipients of list ORACLE-L
>
>
>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
>also send the HELP command for other information (like subscribing).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Diana Duncan
> INET: Diana_at_fileFRENZY.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 - 17:10:10 CDT
 |
 |