CroosJoin - SQL Server Forums

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

CroosJoin

Author

Topic

jcanistrum
Starting Member

2 Posts

Posted - 2008-11-09 : 04:35:39

Hi,

I´ve been trying to figure out how to solve this problem without success

I have one table for Vendors, Produtcs and one with VendorId, ProductID, ItensSold

Vendor ( ID, .... )
Product( ID, .... )
Sold ( Vendor.ID , Product.ID, ItensSold )

and I´d like to produce an output in matrix layout with the cartesian product of Vendor x Product and its ItensSold value even if there is not record associated in the Sold table, something like this :

Vendor , Product1 , Product2, ..., Product n

V1 , 10 , - , , 20
V2 , 20 , 5 , , 5
V3 , 5 , 4 , , -

I´ve got one ugly solution if I know previously the product list using left outer join and union but doing so I need to enumerate each Product in the Where clause.

Thanks in advance,

jcarlos

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 04:53:29

[code]DECLARE @ProductIDList varchar(8000),@Sql varchar(8000)
SELECT @ProductIDList=COALESCE(@ProductIDList,'')+CAST(ProductID AS varchar(10)) + ','
FROM Product

SET @Sql='SELECT *
FROM
(
SELECT m.VendorID,m.ProductID,s.ItemsSold
FROM (SELECT v.VendorName,v.VendorID,p.ProductName,p.ProductID
FROM Vendor v
CROSS JOIN Product p)m
LEFT JOIN Sold s
ON s.VendorID=m.VendorID
AND s.ProductID=m.ProductID
)m
PIVOT(SUM(ItemsSold) FOR ProductID IN (['+REPLACE(@ProductIDList,',','],[') + ']))p'

EXEC (@Sql)
[/code]

jcanistrum
Starting Member

2 Posts

Posted - 2008-11-09 : 05:07:28

thanks a lot ...

but as I see SUM(ItemsSold) in your solution I think I didn´t let it clear that the table SOLD will have only or 0 or 1 record with the pair

Vendor.Id, Product.ID, ItensSold

It is already the result from previuos agregation.

quote:

Originally posted by visakh16

DECLARE @ProductIDList varchar(8000),@Sql varchar(8000)
SELECT @ProductIDList=COALESCE(@ProductIDList,'')+CAST(ProductID AS varchar(10)) + ','
FROM Product

SET @Sql='SELECT *
FROM
(
SELECT m.VendorID,m.ProductID,s.ItemsSold
FROM (SELECT v.VendorName,v.VendorID,p.ProductName,p.ProductID
      FROM Vendor v
      CROSS JOIN Product p)m
LEFT JOIN Sold s
ON s.VendorID=m.VendorID
AND s.ProductID=m.ProductID
)m
PIVOT(SUM(ItemsSold) FOR ProductID IN (['+REPLACE(@ProductIDList,',','],[') + ']))p'

EXEC (@Sql)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-09 : 05:12:53

even then no probelm. PIVOT requires you to do some kind of aggregation thats why i put SUM(). you can even use MIN() or MAX() if you have just one record per pair.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources