Rows into Columns 1

[sarr]

i have a table like this

product | price | status
--------|--------| ------
pen | 100 | old
pen | 105 | new
--------|--------|-------

Expect result as :-
-----------------

product oldprice newprice
------- -------- ---------
pen 100 105

Please note that the resultset should have column name similar to this.

Can a select query be written to do this?

Because i have to write a similar query like this to fetch the results from another table and finally combine these two results into one ( Like using UNION ALL).

 

[tlbroadbent]

Here is one method that works very well for limited number of result columns. It can become cumbersome with many columns.

Select
Product,
sum(Case
When Status='Old' Then Price
Else 0
End) As OldPrice,
sum(Case
When Status='New' Then Price
Else 0
End) As NewPrice
From table_name
Group By Product Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[sarr]

tlbroadbent,
Thanks for your quick reply. This is what i want. You got my vote!

 

[sarr]

What if there should be difference be listed in the 4th column like newprice-oldprice. Directly doing like this is not working for me

 

[tlbroadbent]

You need to use the original query as a sub query in the following manner. Trying to do the difference calculation in the same query is more complex.

Select
Product, OldPrice, NewPrice,
NewPrice-OldPrice As PriceDiff
From
(Select
Product,
sum(Case
When Status='Old' Then Price
Else 0
End) As OldPrice,
sum(Case
When Status='New' Then Price
Else 0
End) As NewPrice
From table_name
Group By Product) As qry Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.