This is called a cross-tab, and it really should be done on the client. They are much better suited to this kind of task.
There are a number of engine-specific ways to do cross-tabs. The problem is that they are engine specific, so one that works on Oracle won't work on DB2 or on SQL Server. We'll leave them out of this discussion for the moment.
There is a portable way to do it, as long as you have no "ties" (in your case that would be duplicate ProductName values) and a small, fixed maximum number of possible cross-tab values. That goes something like:
Code:
SELECT a.Type
, Min(b1.ProductName)
+ Coalesce(', ' + Min(b2.ProductName), '')
+ Coalesce(', ' + Min(b3.ProductName), '')
FROM TableA AS a
INNER JOIN TableB AS b1
ON (b1.ProductID = a.ProductID)
LEFT OUTER JOIN TableB AS b2
ON (b2.productID = a.ProductID
AND b1.ProductName < b2.ProductName)
LEFT OUTER JOIN TableB AS b3
ON (b3.productID = a.ProductID
AND b2.ProductName < b3.ProductName)
WHERE 2 = a.Type
GROUP BY a.Type
You can expand this to get more than three values if needed.
-PatP
dBforums
>
display result in one row
05-07-04, 10:20
Linear Mode
