Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: Combining Rows

Re: Combining Rows

From: Myron Wintonyk <mwintony_at_med.ualberta.ca>
Date: Fri, 16 Feb 2001 14:41:18 +1000
Message-ID: <3A8CAF6D.6BCD6B93@med.ualberta.ca>

Assuming there are never more than 3 "ItemIDs", this will work:

select ID, sum( decode( itemid, 1, AMOUNT, 0 )),

             sum( decode( itemid, 2, AMOUNT, 0 )),
             sum( decode( itemid, 3, AMOUNT, 0 )) from X2
       group by ID;


"Brian Y." wrote:

> Here's a dilemma. I need to combine rows in such a way that I break values
> from the rows into separate columns. I know there's a better way to present
> the data, but this is what's required. Consider these two tables...
>
> Table1
> -------
> ID Name
> 1 Joe
> 2 Fred
> 3 Lisa
>
> and
>
> Table2
> ----------------
> ID ItemID Amount
> 1 1 20
> 1 2 40
> 1 3 30
> 2 1 10
> 2 2 10
> 2 3 50
> 3 1 25
>
> I need to link the tables and combine the rows in such a way that I get
> this...
>
> ID Amount1 Amount2 Amount3
> 1 20 40 30
> 2 10 10 50
> 3 25
>
> Any thoughts on query syntax that will do this? This is Oracle 8i. Thanks in
> advance.
>
> B~
Received on Thu Feb 15 2001 - 22:41:18 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US