How to show multiple rows as columns

[n2jesus]

Hello,

My database stores quantity prices breaks for an item in rows, ie.

index item qty price
1 1 5 10.50
2 1 10 8.75
3 2 7 12.00
4 2 12 11.30
5 3 6 5.00

up to 4 price breaks per items.

I need it to look like this:
item qty1 price1 qty2 price2 qty3 price3 qty4 price4
1 5 10.50 10 8.75
2 7 12.00 12 11.30
3 6 5.00

Not every item has price breaks, and not every item has 4 price breaks.

This needs to be done only using T-sql, and I have to export the query results to excel as a price sheet for our customers.

The excel part isn't the problem, but getting each quantity and price into its own column is a problem.

If anyone has any thoughts on this, please respond.

Thanks,

Jim

 

[SQLDenis]

Take a look here

http://www.aspfaq.com/show.asp?id=2462

http://sqlservercode.blogspot.com/

 

[mrdenny]

search the forum for crosstab queries.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com