SQL tables' cartesian product

[uzeyir]

I need a help to solve following interesting problem for me.

I've 2 id-name tables:
*************** *************** *********
ct (customers_tabl e)
------------------------------
id name
1 Alice
2 Bob
3 Carl
4 John
*************** *************** *********
*************** *************** *********
ft (fruits_table)
-----------------------------
id name
1 Apple
2 Banana
3 Apricot
*************** *************** *********

And 3rd table that consists of data that tells us which customer bought which fruit and how much. For example:
*************** *************** *********
ot (orders_table)
------------------------------
id ct_id ft_id mass
1 1 1 12
2 1 2 10
3 2 1 5
4 3 3 6
5 1 3 13
6 1 1 5
7 2 3 15
8 3 2 4
9 3 2 11
*************** *************** *********

I need the result of total bought fruits, like a following table:
*************** *************** *************** *************
Cust_name Apple Banana Apricot
Alice 17 10 13
Bob 5 0 15
Carl 0 15 6
John 0 0 0
*************** *************** *************** *************

Beforehand thank you very much for your help creating the latter dynamic table...

[deepuv04]

use the following query

SELECT Cust_Name,[Apple],[Apricot],[Banana]
FROM	(
			SELECT  ct.Name as Cust_Name,ft.name as ft_Name,SUM(ot.mass) AS Mass
			FROM	 CT INNER JOIN
					 OT ON CT.ID = OT.Ct_Id INNER JOIN
					 FT ON FT.ID = OT.ft_Id
			GROUP BY CT.Name,ft.Name 
		) src
PIVOT	(SUM(Mass) FOR FT_Name
		IN([Apple],[Apricot],[Banana])) AS pvt

Note: make the query as dynamic string to the values dynamicallu

[uzeyir]

Thank you very much deepuv04 for your excellent answer !!!

To be honestly, I've never heard that SQL has a PIVOT capability !! You solved me a problem, and I'll share this PIVOT and UNPIVOT property to my friends...

Again thanks a lot,
best regards Uzeyir Suleymanov,
Baku, Azerbaijan.