display result in one row

Hi,

I have two tables:

TableA has two columns: Type, ProductID
TableB has two columns: ProductID, ProductName

I need to get all the ProductName for a specific type and this is my SQL:
select TableB.ProductName
from TableA, TableB
where TableA.ProductID = TableB.ProductID
and TableA.Type = 2

The problem with the above SQL is that it returns one column with a few rows, like the following:

ProductA
ProductB
ProductC

Is there a way to have a single SQL that can return the results horizontal like:

ProductA, ProductB, ProductC

Thank you for help in advance.

SnoopyI don't think this is possible only through a single SQL.|||If not, what do you recommend to do? Thanks!|||Can you provide more info?
What DB, are you using a program to run this SQL??
Would it be possible for you load results into a data structure and format accordingly??|||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: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.TypeYou can expand this to get more than three values if needed.

-PatP