# Re: Query Question

Date: Mon, 15 Sep 2008 08:04:25 -0700 (PDT)

Message-ID: <346e5397-9b5f-464f-b7eb-2a32a1ff702d@b1g2000hsg.googlegroups.com>

On Sep 14, 1:45 pm, e..._at_chicagorsvp.com wrote:

*> On Sep 14, 1:13 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:*

*>*

*>*

*>*

*>*

*>*

*> > On Sep 14, 1:30 pm, e..._at_chicagorsvp.com wrote:*

*>*

*> > > Hi,*

*>*

*> > > I have a column with the following values: A or D*

*>*

*> > > What I want is to have those values in the same linme like this:*

*>*

*> > > A (some data), D (some data)*

*>*

*> > > How can I get each value as a different column??*

*>*

*> > > Thanks!*

*>*

*> > Not quite sure by what you mean by "A (some data), D (some data)" can*

*> > you give a more detailed example. Include several rows that have A or*

*> > D values in that column as base data and give us exactly what you want*

*> > to see.*

*>*

*> > You can always select the same column twice in the same SELECT ( and*

*> > then say use 2 different DECODE's on it for example ) ... is that what*

*> > you are asking?*

*>*

*> Well, say for example the data in my table looks like this:*

*>*

*> C1 C2*

*> - ----------*

*> A 56*

*> A 11*

*> D 76*

*> D 33*

*>*

*> select decode(c1,'A','A'), sum(c2), decode (c1,'D','D'), sum(c2) from*

*> y group by c1;*

*>*

*> D SUM(C2) D SUM(C2)*

*> - ---------- - ----------*

*> A 67 67*

*> 109 D 109*

*>*

*> See, you actually get 2 records when I want 1 record with the values*

*> across:*

*>*

*> A 67 D 109*

*>*

*> Does that make more sense?- Hide quoted text -*

*>*

*> - Show quoted text -*

Certainly it does, but you won't get that result from using DECODE() without some rather interesting gyrations:

SQL> column S_A new_value colA noprint SQL> column S_D new_value colD noprint SQL> SQL> select sum(c2) S_A

2 from y

3 where c1= 'A'

4 group by c1;

**SQL>
**

SQL> select sum(c2) S_D

2 from y

3 where c1= 'D'

4 group by c1;

**SQL>
**

SQL> select c_a, a, c_d, d

2 from

3 (select distinct case when c1='A' then 'A' end C_A,
nvl(sum(decode(c1, 'A', c2)), &colA) A,

4 case when c1='A' then 'D' end C_D, nvl(sum(decode(c1, 'D', c2)),
&colD) D

5 from y

6 group by c1)

7 where c_a is not null and c_d is not null;

C A C D

- ---------- - ----------

A 67 D 109

**SQL>
**
It's much easier for you to learn how to write a query using the WITH
syntax:

SQL> with a as(

2 select c1 c1a, sum(c2) sum_a 3 from y 4 where c1 = 'A' 5 group by c1

6 ),

7 d as (

8 select c1 c1d, sum(c2) sum_d 9 from y 10 where c1 = 'D' 11 group by c1

12 )

13 select c1a, sum_a, c1d, sum_d

14 from a, d;

**C SUM_A C SUM_D
**

- ---------- - ----------

A 67 D 109

**SQL>
**
David Fitzjarrell
Received on Mon Sep 15 2008 - 10:04:25 CDT