How to show row output as column output

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. Received on Wed Mar 08 2006 - 11:23:05 CST