Newsgroups: comp.databases.oracle.misc Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!news.glorb.com!sws1!ornl!news.er.usgs.gov!news From: Brian Peasland Subject: Re: merging two rows In-Reply-To: <1159362645.688989.262200@m7g2000cwm.googlegroups.com> X-Nntp-Posting-Host: edc-cv-160-68.cr.usgs.gov Content-Type: text/plain; charset=ISO-8859-1; format=flowed Message-ID: User-Agent: Thunderbird 1.5.0.7 (Windows/20060909) Sender: news@igsrsparc2.er.usgs.gov (Janet Walz (GD) x6739) Content-Transfer-Encoding: 7bit Organization: U.S. Geological Survey, Reston VA References: <1159304397.241596.271210@b28g2000cwb.googlegroups.com> <1159362645.688989.262200@m7g2000cwm.googlegroups.com> Mime-Version: 1.0 Date: Wed, 27 Sep 2006 13:21:33 GMT Lines: 50 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:130515 > 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 > Is the NAME field unique? If so, then a self join as follows would work: SELECT t1.name, t1.account,t1.type,t1.code, t2.account,t2.type,t2.code FROM table t1, table t2 WHERE t1.name=t2.name AND t1.account='CHECKING' and t2.account='SAVINGS'; In your case, a GROUP BY is not the correct clause for the job..based on what you have provided so far. If the NAME field is not unique, then how do you determine which rows get "merged" with other rows? HTH, Brian -- =================================================================== Brian Peasland dba@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