Creating a report with customers as rows and products as columns

[hasan939]

A table named SELL have 3 fields---
customer_id,product,unit

the rows of the table ---------
customer_id----product---unit
---------------------------------------------
1-----------------toy--------------15
2-----------------toy--------------20
3----------------food-------------25
4----------------dress---------------10
2----------------toy----------------12
2----------------food----------------5
1----------------food---------------3


I want to make a sql so that the data will be shown as followings:

customer_id------toy--------food----dress-----TOTAL
-------------------------------------------------------------
1----------------------15--------3---------0----------18
2---------------------32--------5----------0----------37
3---------------------0----------25--------0----------25
4---------------------0----------0----------10--------10
5--------------------0-----------0-----------0----------0
_____________________________________
TOTAL------------47--------33--------10----------90


customer_id is the foreign key which refers another table Customer.
and product is also foreign key which refers another table PRODUCT.

Now please help me to make the query.

[r035198x]

A table named SELL have 3 fields---
customer_id,product,unit

the rows of the table ---------
customer_id----product---unit
---------------------------------------------
1-----------------toy--------------15
2-----------------toy--------------20
3----------------food-------------25
4----------------dress---------------10
2----------------toy----------------12
2----------------food----------------5
1----------------food---------------3


I want to make a sql so that the data will be shown as followings:

customer_id------toy--------food----dress-----TOTAL
-------------------------------------------------------------
1----------------------15--------3---------0----------18
2---------------------32--------5----------0----------37
3---------------------0----------25--------0----------25
4---------------------0----------0----------10--------10
5--------------------0-----------0-----------0----------0
_____________________________________
TOTAL------------47--------33--------10----------90


customer_id is the foreign key which refers another table Customer.
and product is also foreign key which refers another table PRODUCT.

Now please help me to make the query.

If you are going to have as many columns as products, how are you going to know how many columns you will need?

[pbmods]

Changed thread title to better describe the problem (did you know that threads whose titles do not follow the Posting Guidelines actually get FEWER responses?).

[hasan939]

Actually I am making a database software.
Now I just create a table with the fields customer and with distinct product name.
that means if number of distinct product=5 then I create a table with 6 column.
Then I insert rows for every customer and curresponding total product wise unit for each customer.

I have done it in the run time of the software.

But I think it is not the actual way.

Yes there any other efficient way?

Thanks to all.
BYE