Re: enumerate domain of column as new columns.. count

Thanks for the pat on the back. BTW he's not my intern.

But you did catch me without access to ORACLE. I usually let the compiler/interpreter catch missing parentheses and save my few brain cells for the logic of the problem.

Have a good day.

   ED

TurkBear wrote:
>
> Pretty slick Ed, I guess I'll have to put my shortcut to Tom Kyte's site back..
> BTW, you ( or your intern) missed the needed Right Parens on each line..
> i.e. count(decode(apptype,'A','X',NULL)) as A, not count(decode(apptype,'A','X',NULL) as A,
>
> Thanks ...
>
> Ed Prochak <edprochak_at_magicinterface.com> wrote:
>
> >
> >Okay, I'll spell it out for you. He has a fixed number of columns,
> > so set up a decode for each one.
> >
> >SELECT app,
> > count(decode(apptype,'A','X',NULL) as A,
> > count(decode(apptype,'B','X',NULL) as B,
> > count(decode(apptype,'C','X',NULL) as C,
> > count(decode(apptype,'A','X','B','X','C','X',NULL) as Total
> >FROM table1 group by app ;
> >
> >
> >Note I had all the decodes return 'X' to emphsize that count()
> > doesn't care what the value is, as long as it is NOT NULL.
> > With a little practice even a lowly intern can do it. Of course,
> > the whole query needs to be touched if another APPTYPE is needed
> > in the report. But this is the simple solution for the simple case.
> >
> >
> > (Tom Kyte has shown this may times, check his web site.
> >Thomas Kyte
> >tkyte_at_us.oracle.com
> >Oracle Government
> >Bethesda MD
> >
> >http://govt.us.oracle.com/ -- downloadable utilities)
> >
> >Final note: with version 8 and above, you may be able to use CASE WHEN
> >
> >TurkBear wrote:
> >>
> >> Ed, A multi-word question:
> >> How can DECODE produce a cross-tab?
> >>
> >> Ed Prochak <edprochak_at_magicinterface.com> wrote:
> >>
> >> >
> >> >This is a relatively simple case, so I have a one word suggestions for
> >> >you: DECODE()
> >> >
> >> >(IOW, check out using the decode function.)
> >> >
> >> > ed
> >> >
> >> >tone wrote:
> >> >>
> >> >> hey everyone.. i cant figure out the answer to this. Id like to do it
> >> >> in SQL/PLSQL if it can be done. ie. i would rather not have to mess
> >> >> with java..
> >> >> this is the result of a select statement (select app, apptype,
> >> >> count(*) from table1 group by app, apptype;)
> >> >>
> >> >> app type count
> >> >> app1 A 3
> >> >> app1 B 2
> >> >> app2 A 1
> >> >> app2 C 2
> >> >> ...
> >> >>
> >> >> im trying to get it to look like this
> >> >>
> >> >> app A B C Total
> >> >> app1 3 2 0 5
> >> >> app2 1 0 2 3
> >> >>
> >> >> and for the life of me, i cant figure it out.. i cant even describe
> >> >> the problem in db terms.
> >> >>
> >> >> can someone, anyone help me.. ???
> >> >> -confused intern at big company..
> >> >> ps. i have to go fetch the coffee now. the life of an intern. ya gotta
> >> >> love it!
>
> -----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----

-- 
Edward J. Prochak   --- Magic Interface, Ltd.
Ofc: 440-498-3700
on the web at       --- http://www.magicinterface.com
email: ed.prochak_at_magicinterface.com