SQL tables' cartesian product

[uzeyir]

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

I've 2 id-name tables:
***************************************
ct (customers_table)
------------------------------
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.