Re: Query Question

From: <fitzjarrell_at_cox.net>
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