Pivot with sorted column totals

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pivot with sorted column totals

Author

Topic

Carlsson
Starting Member

3 Posts

Posted - 2010-05-21 : 07:14:35

Hi,

I run a price search engine for plumbing equipment. Usually users need a range of items but the cheapest price on each item often span different retailers. Im working on a "Shopping List" to which users can add the items they need. The shopping list will display the items and their prices at the different retailers and the total price making it easy to choose the retailer who is the cheapest with the items all together.

Im looking to get this result:

ItemName | ItemNo | Quantity | Retailer1 | Retailer2
-----------------------------------------------------
Tube       |   100    |     1      |    25,00   |   32,00
Packing    |   200    |     2      |    50,00   |   40,00
-----------------------------------------------------
                               TOTAL |    75,00   |   72,00

Server: MS SQL Server 2005

TABLES involved:

ShoppingListItems
ItemID
Quantity

Items
ItemID
ItemNo
Title
DateLastCrawled

ItemPrices
ItemID
ShopID
Price
DateCrawled

Shops
ShopID
ShopName

------------------------------------------------------------

QUERY:
If have succeded in pivoting the result, but i cant get the "Total" row working. Also I would like to sort columns by the Total value, making to overall cheapest retailer come first after the columns "Title", "ItemNo", "Quantity" -> "Cheapest retailer" etc.

SELECT Title, ItemNo, Quantity, [Retailer1], [Retailer2] FROM
(
SELECT Title, ItemNo, Price, Quantity, ShopName
FROM ShoppingListItems
INNER JOIN Items
ON Items.ItemID = ShoppingListItems.ItemID
INNER JOIN ItemPrices
ON Items.ItemID = ItemPrices.ItemID
INNER JOIN Shops
ON Shops.ShopID = ItemPrices.ShopID
WHERE
ShoppingListItems.ShoppingListID = ' + CAST(@ShoppingListID AS nvarchar(5)) + '
AND
ItemPrices.DateCreated = Items.DateLastCrawl
) AS a
PIVOT
(
SUM(Price) FOR ShopName IN ([Retailer1], [Retailer2])
) AS b

Hope someone can give me directions on what to do.

Kind regards
Bo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources