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

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 =----- Received on Tue Jul 16 2002 - 10:53:50 CDT