Product price comparison in sql

问题:

I have a table looks like given below query, I add products price in this table daily, with different sellers name :

create table Product_Price
    (
      id int,
      dt date,
      SellerName varchar(20),
      Product varchar(10),
      Price money
    )

    insert into Product_Price values (1, '2012-01-16','Sears','AA', 32)
    insert into Product_Price values (2, '2012-01-16','Amazon', 'AA', 40)
    insert into Product_Price values (3, '2012-01-16','eBay','AA', 27)

    insert into Product_Price values (4, '2012-01-17','Sears','BC', 33.2)
    insert into Product_Price values (5, '2012-01-17','Amazon', 'BC',30)
    insert into Product_Price values (6, '2012-01-17','eBay', 'BC',51.4)

    insert into Product_Price values (7, '2012-01-18','Sears','DE', 13.5)
    insert into Product_Price values (8, '2012-01-18','Amazon','DE', 11.1)
    insert into Product_Price values (9, '2012-01-18', 'eBay','DE', 9.4)

I want result like this for n number of sellers(As more sellers added in table)

DT           PRODUCT   Sears[My Site]   Amazon   Ebay   Lowest Price
1/16/2012    AA        32               40       27     Ebay
1/17/2012    BC        33.2             30       51.4   Amazon
1/18/2012    DE        7.5              11.1     9.4    Sears

回答1:

I think this is what you're looking for.

SQLFiddle

It's kind of ugly, but here's a little breakdown.

This block allows you to get a dynamic list of your values. (Can't remember who I stole this from, but it's awesome. Without this, pivot really isn't any better than a big giant case statement approach to this.)

DECLARE @cols AS VARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' +
                        QUOTENAME(SellerName)
                      FROM Product_Price
                      FOR XML PATH(''), TYPE
                     ).value('.', 'NVARCHAR(MAX)') 
                        , 1, 1, '')

Your @cols variable comes out like so:

[Amazon],[eBay],[Sears]

Then you need to build a string of your entire query:

select @query = 
'select piv1.*, tt.sellername from (
select *
from
(select dt, product, SellerName,  sum(price) as price from product_price group by  dt, product, SellerName) t1

pivot (sum(price) for SellerName in (' + @cols + '))as bob
) piv1
inner join
(select t2.dt,t2.sellername,t1.min_price from
(select dt,  min(price) as min_price  from product_price group by  dt) t1
inner join (select dt,sellername, sum(price) as price from product_price group by dt,sellername) t2 on t1.min_price = t2.price) tt
on piv1.dt = tt.dt
'

The piv1 derived table gets you the pivoted values. The cleverly named tt derived table gets you the seller who has the minimum sales for each day. (Told you it was kind of ugly.)

And finally, you run your query:

execute(@query)

And you get:

 DT     PRODUCT     AMAZON  EBAY    SEARS   SELLERNAME
2012-01-16  AA  40  27  32  eBay
2012-01-17  BC  30  51.4    33.2    Amazon
2012-01-18  DE  11.1    9.4     13.5    eBay

(sorry, can't make that bit line up).

I would think that if you have a reporting tool that can do crosstabs, this would be a heck of a lot easier to do there.

回答2:

The problem is this requirement:

I want result like this for n number of sellers

If you have a fixed, known number of columns for your results, there are several techniques to PIVOT your data. But if the number of columns is not known, you're in trouble. The SQL language really wants you to be able to describe the exact nature of the result set for the select list in terms of the number and types of columns up front.

It sounds like you can't do that. This leaves you with two options:

1. Query the data to know how many stores you have and their names, and then use that information to build a dynamic sql statement.
2. (Preferred option) Perform the pivot in client code.

回答3:

This is something that would probably work well with a PIVOT. Microsoft's docs are actually pretty useful on PIVOT and UNPIVOT.

http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Basically it allows you to pick a column, in your case SellerName, and pivot that out so that the elements of the column themselves become columns in the new result. The values that go in the new "Ebay", "Amazon", etc. columns would be an aggregate that you choose - in this case the MAX or MIN or AVG of the price.

For the final "Lowest Price" column you'd likely be best served by doing a subquery in your main query which finds the lowest value per product/date and then joining that back in to get the SellerName. Something like:

SELECT 
    Product_Price.Date
    ,Product_Price.Product
    ,Product_Price.MinimumSellerName
FROM
(SELECT 
MIN(Price) AS min_price
,Product
,Date
FROM Product_Price
GROUP BY
Product
,Date) min_price
INNER JOIN Product_Price
ON min_price.Product = Product_Price.Product
    AND min_price.Date = Product_Price.Date

Then just put the pivot around that and include the MinimumSellerName columnm, just like you include date and product.