Select multiple rows in single row view

[Juan Pablo G C]

I suppose is a typical query but I dont know if exists a query to solve the next. Take for instance: 1.- Table items: Code|Name 1000 Apple 1001 Orange 1002 Banana 2.- Table prices Shop|Code|Price 1 1000 1.33 2 1000 1.44 1 1001 1.90 2 1001 2.10... Can I show in a SQL query something LIKE: Code Name PriceShop1 PriceShop2 1000 Apple 1.33 1.44 1001 Orange 1.90 2.10.... Thanks for any help because Im getting crazy

Juan Pablo G.C. Overrider Blog

[andyharman]

If you just have 2 shops then:

select i.Code, i.Name, p1.Price as PriceShop1, p2.Price as PriceShop2
from Items i
left outer join Prices as p1
  on p1.Code = i.Code
  and p1.Shop = 1
left outer join Prices as p2
  on p2.Code = i.Code
  and p2.Shop = 2
order by i.Code

This links to the Prices table twice (one for shop 1 and once for shop 2). The "p1" and "p2" bits are "alias" names for these joins. If you have more shops then you should try searching google for "crosstab queries". Regards Andy

[Paddy Boyd]

If you are in SQL2005 you should look at the PIVOT command.

[Juan Pablo G C]

OK I'll take a look, thanks

Juan Pablo G.C. Overrider Blog

[Venkataramuc]

sorry friend .i am getting exact ans, iam geting this one. SELECT i.Item, p.shop FROM Items i, prices p WHERE i.code = p.code GROUP BY i.Item, p.shop regards venkat