Re: How to show row output as column output

Krishna wrote:
> Hi
>
> I know this question has been posted many times and I have gone thru
> all of them and tried using decode also. But I am still not able to
> get the desired output. Here are the details.
>
> I have the following table
>
> TYPE Product Description Product code
> Sweet Cake 1
> Hot Burger 2
> Hot Pizza 3
> Sweet Pasta 4
> Hot Wrap 5
>
> Type is either sweet or hot. But Product description can be both sweet
> and hot. Product description has got nothing to do with the type of
> product for instance there can also be a sweet burger and or hot pasta.
> I want to see the number of sweet burgers made in a given day or for
> that matter any particular item made in a given day.
>
> I am giving the following command.
>
> SELECT A.TYPE, A.CAKE, B.BURGER, C.PIZZA, D.PASTA, E.WRAP,
> (SELECT TYPE, COUNT(TYPE) CAKE FROM PROD_DESC
> WHERE PROD_CODE = 1
> GROUP BY TYPE) A,
> SELECT TYPE, COUNT(TYPE) BURGER FROM PROD_DESC
> WHERE PROD_CODE = 2
> GROUP BY TYPE) B,
> SELECT TYPE, COUNT(TYPE) PIZZA FROM PROD_DESC
> WHERE PROD_CODE = 3
> GROUP BY TYPE) C
> WHERE A.TYPE = B.TYPE AND
> B.TYPE = C.TYPE
>
> The output is coming as follows:
>
> Type Cake Burger Pizza Pasta
> Hot 50 25 30 60
> Sweet 35 40 70 80
>
> But what I would like to see is
>
> Type Hot Cake Sweet Cake Hot Burger Sweet Burger Hot Pizza Sweet
> Pizza Hot Pasta
> Total 50 35 25 40 30 70 60
>
>
> Any help would be highly appreciated.
>
> Thank you.

Now back to your problem. Go to Morgan's Library at www.psoug.org. Scroll down to DECODE. Work your way through the examples one by one and the material from which the answer can be constructed will likely present itself.

And yes, regulars, it is Finals week.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)