need Sql query (may be pivot)

Question

Hi guys,

i have table like below ,

Product Name   Price   Date
Apple          1.5     5/5/2009
Apple          3       5/6/2009
Apple          3.5     5/7/2009
Apple          2.5     5/8/2009
Apple          5.5     5/9/2009
Orange         10.5    5/5/2009
Orange         12.5    5/6/2009
Orange         7.5     5/7/2009
Orange         4.5     5/8/2009
Orange         5.5     5/9/2009

I need output like below:

Product Name  5/5/2009   5/6/2009    5/7/2009    5/8/2009  5/9/2009
Apple          1.5        3          3.5         2.5       5.5
Orange        10.5       12.5        7.5         4.5       5.5

also date increases column also need to increase, Pls help me

Vickees

Answer 1

the answer depends on which database you are using, but you may benefit from this post

please note that this is a very popular question on SO. you would be best served by searching for related topics. i think there you will have the benefit of many different perspectives, not just those of the users logged in at this moment.

Answer 2

select [Product name], //Give me all of the Fruit
sum(case when date = '5/05/09' then Price end as [5/5/09], // Sum Case Allow for multiple record on the same day
sum(case when date = '5/06/09' then Price end as [5/6/09],
sum(case when date = '5/07/09' then Price end as [5/7/09],
sum(case when date = '5/08/09' then Price end as [5/8/09],
sum(case when date = '5/09/09' then Price end as [5/9/09]
from tblfruits
group by [Product Name]