Re: merging two rows

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 27 Sep 2006 10:09:22 -0700
Message-ID: <1159376962.398712@bubbleator.drizzle.com>

sunil(nani) wrote:
> Brian Peasland wrote:

>> sunil(nani) wrote:
>>> can i merge two different rows into single row in a situations
>>> where i can not use group by clause
>>>
>> I assume that since you referenced the GROUP BY clause, then you are
>> talking about doing this in SQL. But I'm still not sure what you are
>> attempting to do here. If you are using SQL, then why not use the GROUP
>> BY clause? Do you care to provide a more specific example?
>>
>> Otherwise, there are methods to "merge" two rows into a single row in
>> the result set. Many aggregate functions (MIN, MAX, etc) do just this
>> and you are only required to use the GROUP BY clause when you are
>> returning columns that will not participate in the aggregation. You can
>> also "merge" two rows by doing a simple JOIN operation:
>>
>> SELECT t1.x, t2.x
>> FROM table_x t1, tablex t2
>> WHERE t1.id = t2.id;
>>
>> HTH,
>> Brian
>>
>>
>> --
>> ===================================================================
>>
>> Brian Peasland
>> dba_at_nospam.peasland.net
>> http://www.peasland.net
>>
>> Remove the "nospam." from the email address to email me.
>>
>>
>> "I can give it to you cheap, quick, and good.
>> Now pick two out of the three" - Unknown

>
>
>
>
>
>
>
>
>
>
>
> thank you for u r reply
> i am using oracle 9i
>
>
> for example i have two rows in this manner
>
> id name account type code
>
> 1 sunil checking x a1
> 2 sunil savings y a2
>
> can i get in this way by merging two rows
>
> id name account type code account1 type1 code 1
>
> 1 sunil checking x a1 savings y a2
>
> i tried to do self join also o think it does not work since there is
> one primary key called id
> and there is no chance of group by also
>
> thank you
> sunil

Use Brian's solution but assuming your column names are real also do this:

SELECT COUNT(*)
FROM gv$reserved_words
WHERE keyword IN ('ID', 'NAME', 'ACCOUNT', 'TYPE');

4 out of 4.

-- 
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group

Received on Wed Sep 27 2006 - 12:09:22 CDT