pivot a table

[abware]

hi.
this is a very hard sql.
if someone can do it without a cursor and in 1 sql, it will be very good.

this is my table:
item | store | Price
aaa |store1 | 20
aaa |store8 | 19
aaa |store6 | 50
aaa |store55| 64
aaa |store3 | 15
bbb |store1 | 12
bbb |store8 | 20
bbb |store6 | 15
bbb |store55| 16
bbb |store3 | 20

i want the sql to select the 3 lowest prices per item (only the lowes 3), and show each item in a column, like this:

item | Store1 | Price1 | store2 | Price2 | Store3 | Price3
-------------------------------------------------------------
aaa |store3 |15.00 | store8 | 19.00 | store1 | 20.00
bbb |store1 |12.00 | store6 |15.00 |store55 | 16.00

thanks

 

[AlexCuse]

This should be asked in the forum for your specific DBMS. Your answer is likely going to be some type of dynamically-generated SQL, unless you have a fixed number of columns that you will want to display.

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Something like this ?

SELECT X.Item, X.Store AS Store1, X.Price AS Price1, Y.Store AS Store2, Y.Price AS Price2, Z.Store AS Store3, Z.Price AS Price3
FROM (SELECT A.Item, A.Store, A.Price
FROM myTable A INNER JOIN myTable B ON A.Item=B.Item AND A.Price>=B.Price
GROUP BY A.Item, A.Store, A.Price HAVING Count(*)=1
) X
INNER JOIN (SELECT A.Item, A.Store, A.Price
FROM myTable A INNER JOIN myTable B ON A.Item=B.Item AND A.Price>=B.Price
GROUP BY A.Item, A.Store, A.Price HAVING Count(*)=2
) Y ON X.Item=Y.Item
INNER JOIN (SELECT A.Item, A.Store, A.Price
FROM myTable A INNER JOIN myTable B ON A.Item=B.Item AND A.Price>=B.Price
GROUP BY A.Item, A.Store, A.Price HAVING Count(*)=3
) Z ON X.Item=Z.Item

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[abware]

got it.
thank you very much.