Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itaybarda
Starting Member
12 Posts |
 Posted - 2007-05-16 : 11:20:05
|
| 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 | Priceaaa |store1 | 20aaa |store8 | 19aaa |store6 | 50aaa |store55| 64aaa |store3 | 15bbb |store1 | 12bbb |store8 | 20bbb |store6 | 15bbb |store55| 16bbb |store3 | 20i 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.00bbb |store1 |12.00 | store6 |15.00 |store55 | 16.00thanks |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-05-16 : 11:29:55
|
| Read about [bCross-tab Reports[/b] in sql server help fileMadhivananFailing to plan is Planning to fail |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-16 : 11:32:13
|
| Use the new PIVOT operator together with ROW_NUMBER() function.Peter LarssonHelsingborg, Sweden |
|
|
|
itaybarda
Starting Member
12 Posts |
Posted - 2007-05-16 : 11:43:57
|
| Peso. can you give me example of how to do it? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-16 : 11:47:00
|
| See this:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79951&SearchTerms=PIVOT,operator[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-05-16 : 11:50:24
|
| [code]-- prepare sample datadeclare @ table (item varchar(3), store varchar(7), price int)insert @select 'aaa', 'store1', 20 union allselect 'aaa', 'store8', 19 union allselect 'aaa', 'store6', 50 union allselect 'aaa', 'store55', 64 union allselect 'aaa', 'store3', 15 union allselect 'bbb', 'store1', 12 union allselect 'bbb', 'store8', 20 union allselect 'bbb', 'store6', 15 union allselect 'bbb', 'store55', 16 union allselect 'bbb', 'store3', 20-- show the expected resultselect d.item, max(case when d.recid = 1 then d.store end) as store1, max(case when d.recid = 1 then d.price end) as store1, max(case when d.recid = 2 then d.store end) as store2, max(case when d.recid = 2 then d.price end) as store2, max(case when d.recid = 3 then d.store end) as store3, max(case when d.recid = 3 then d.price end) as store3from ( select item, store, price, row_number() over (partition by item order by price) as recid from @ ) as dwhere d.recid between 1 and 3group by d.item[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
itaybarda
Starting Member
12 Posts |
Posted - 2007-05-16 : 12:02:44
|
| WOW....than you very much peso and harsh_athalye..it's a perfectly working sql. very proffessional. |
|
|
|
|
|
|
|
|
pivot a table