Joining new columns in MySQL One-To-Many

Question

I asked a similar question the other day but it seems no one was able to answer it, and searched the internet for a few days but still fruitless, perhaps I am not asking the question the right way: http://stackoverflow.com/questions/3444718/one-to-many-query-in-mysql
So I while try again and maybe word it a bit differently. This is essentially a simplified version of what I am trying to do:

CREATE TABLE Customer(  
customer_id INT NOT NULL,  
first_name varchar(20),  
last_name varchar(20)  
);  
CREATE TABLE Payment(  
customer_id INT NOT NULL,  
amount_paid INT,  
year YEAR,  
FOREIGN KEY (customer_id) REFERENCES Customer(customer_id)  
); 

What I want is to organize the first_name on the left, only occurring once, and then for each year list the payment amount in separate columns because I am going to be attaching this to WPF and I want a spreadsheet style representation of the data. So, ideally it would look like this:

name 2009 2008 2007  
John 500 600 NULL  
Anne NULL 500 600  
Bob NULL NULL 600  

My approach is to count the number of distinct years of payments, and use that as a loop counter. than loop through and collect the data for each year. Represent each column of amount_paid by the year number. I am not just not sure how to do that, because my initial approach was to use UNION, but than I realized that just puts everything in the same column as opposed to separate ones. So what should I be using? I am only asking for some guidance. Thank you!

Answer 1

mysql unfortunately has no pivot feature, so the only possible way is to format the result set with any programming language.

Answer 2

Use:

  SELECT c.first_name,
         MAX(CASE WHEN p.year = 2009 THEN c.amount_paid ELSE NULL END) AS 2009,
         MAX(CASE WHEN p.year = 2008 THEN c.amount_paid ELSE NULL END) AS 2008,
         MAX(CASE WHEN p.year = 2007 THEN c.amount_paid ELSE NULL END) AS 2007
    FROM CUSTOMER c
    JOIN PAYMENT p ON p.customer_id = c.customer_id
GROUP BY c.first_name

Answer 3

I hadn't test it but I think something like this works:

select * from ((select name, sum(amount_paid) as 2009 from customer, payment
where customer.customer_id = payment.customer_id
and payment.year = 2009) a,
(select name, sum(amount_paid) as 2008 from customer, payment
where customer.customer_id = payment.customer_id
and payment.year = 2008) b
(select name, sum(amount_paid) as 2007 from customer, payment
where customer.customer_id = payment.customer_id
and payment.year = 2007) c);