sql server - Add columns to query based on previous queries -

this question has answer here:

• sql server: examples of pivoting string data 6 answers

i have 2 tables:

+-----------+ | customer  | +-----------+ | id | name | +----+------+ | 1  | jack | +----+------+ | 2  | john | +----+------+  +----------------------------------------+ |                  bill                  | +----------------------------------------+ | id | customer_id | date       | amount | +----+-------------+------------+--------+ | 1  | 1           | 01.01.2015 | 10$    | +----+-------------+------------+--------+ | 2  | 1           | 01.01.2014 | 20$    | +----+-------------+------------+--------+ | 3  | 2           | 01.01.2015 | 5$     | +----+-------------+------------+--------+ | 4  | 2           | 01.02.2015 | 50$    | +----+-------------+------------+--------+ | 5  | 2           | 01.01.2014 | 15$    | +----+-------------+------------+--------+ 

i need know sum of bills customer got in year.

that's pretty easy:

select      sum(bill.amount), customer.name      customer  inner join      bill on customer.id = bill.customer_id      bill.date between #20150101# , #20151231# group      customer.name 

the difficult part need display results of query multiple years in single table this:

+-------------------------------------------+ |             sales customer             | +-------------------------------------------+ | customer_id | customer_name | 2015 | 2014 | +-------------+---------------+------+------+ | 1           | jack          | 10$  | 20$  | +-------------+---------------+------+------+ | 2           | john          | 55$  | 20$  | +-------------+---------------+------+------+ 

i'm using sql server 2005.

i'm grateful every answer.

sincerly andahari

as stated need use pivot in order achieve results looking for, this:

select customer_id, customer_name, [2015], [2014] (select customer_id, name customer_name, year(_date) yr, amount bill b inner join customer c on c.id = b.customer_id ) src pivot (sum(amount) yr in ([2015],[2014])) pvt