Drop table #baseTable
Create Table #baseTable(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE CHAR(1))
INSERT #baseTable ( PRODUCTID, PRICEDATE, PRICE, SOURCE )
SELECT 1, '20080102', 10.12, 'A' UNION ALL
SELECT 1, '20080102', 10.13, 'B' UNION ALL
SELECT 1, '20080102', 10.12, 'C' UNION ALL
SELECT 1, '20080102', 10.12, 'D' UNION ALL
SELECT 2, '20080102', 43.23, 'A' UNION ALL
SELECT 2, '20080102', 43.26, 'B' UNION ALL
SELECT 2, '20080102', 43.23, 'C' UNION ALL
SELECT 2, '20080102', 43.26, 'D' UNION ALL
SELECT 3, '20080102', 10.00, 'A' UNION ALL
SELECT 3, '20080102', 10.00, 'B' UNION ALL
SELECT 3, '20080102', 10.01, 'C' UNION ALL
SELECT 3, '20080102', 10.02, 'D'
drop table #groupedProducts
Create Table #groupedProducts(
PRODUCTID INT,
PRICEDATE DATETIME,
PRICE DECIMAL(8,2),
SOURCE CHAR(1),
ExpSource Char(1),
ExpPrice Decimal(8,2),
Done char(1))
Insert into #groupedProducts
select tbl1.*, tbl2.source, tbl2.Price, ''
from #baseTable tbl1
Inner join #baseTable tbl2 on
tbl1.productId = tbl2.productId
and tbl1.price <> tbl2.price
and tbl1.price < tbl2.price
order by tbl1.productID, tbl1.price, tbl2.price
--select distinct productid, price, source, expsource, expprice from #groupedProducts order by productID, price, source, expSource, expPrice
--Create return table
drop Table #outProducts
Create Table #outProducts(
PRODUCTID INT,
PRICE DECIMAL(8,2),
SOURCE CHAR(2000),
ExpSource Char(2000),
ExpPrice Decimal(8,2))
--Create table to hold products to "walk"
drop table #Products
Create Table #Products(
ProductID int,
Price Decimal(8,2),
ExpPrice Decimal(8,2),
done char(1))
--select * from #products
Insert into #Products
Select distinct ProductID, Price, ExpPrice, ''
From #groupedProducts
order by productID
--Setup Loop variables
Declare @flag as varchar(10)
set @flag = 'Not Done'
Declare @Source as varchar(800)
Declare @ExpSource as varchar(800)
Declare @curProductID as int
Declare @curPrice as Decimal(8,2)
Declare @curExpPrice as Decimal(8,2)
While(@flag='Not Done')
Begin
--Clean variables
Select @curProductID = 0, @curPrice = 0, @curExpPrice = 0
--Get next product to work
Select Top 1 @curProductID = ProductID, @curPrice = Price, @curExpPrice = ExpPrice
from #products
where done = ''
Print 'XXXX---'+rtrim(@curProductID)
if @curProductID > 0
Begin
-- Clean the variable to concat all the row info
Select @Source = '' --Add leading text here if needed
-- in the select add the variable to itself and the value in the column and a comma --
select @Source = @Source + cast(a.Source as varchar(200)) + ','
from (
Select distinct Source
from #groupedProducts
where productID = @curProductID
and price = @curPrice
and expPrice = @curExpPrice) as a
-- Retrieve vaules and strip of the last comma--
select @Source = left(@Source,len(@Source)-1)
--Select @Source
--Clean the variable to concat all the row info
Select @ExpSource ='' --Add leading text here if needed
-- in the select add the variable to itself and the value in the column and a comma --
select @ExpSource = @ExpSource + cast(a.ExpSource as varchar(200)) + ','
from (
Select distinct ExpSource
from #groupedProducts
where productID = @curProductID
and price = @curPrice
and ExpPrice = @curExpPrice) as a
-- Retrieve vaules and strip of the last comma--
select @ExpSource = left(@ExpSource,len(@ExpSource)-1)
--Select @ExpSource
--Load Table with values
Insert into #outProducts
Select Distinct b.ProductID, b.Price,
@Source, @ExpSource, b.ExpPrice
FROM #groupedProducts b
where b.ProductID = @curProductID
and b.Price = @curPrice
and b.expPrice = @curExpPrice
update #products
set done='X'
where
ProductID = @curProductID
and Price = @curPrice
and ExpPrice = @curExpPrice
print rtrim(@curProductId) + '-' + rtrim(@curPrice) + '-' + rtrim(@curExpPrice)
End
Else
Begin
set @flag = 'Done'
End
End
Select ProductID, Price, Left(Source,20) as Source, ExpPrice, Left(ExpSource,20) as ExpSource from #outProducts
