Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004

    Unanswered: display result in one row


    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:


    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.


  2. #2
    Join Date
    May 2002
    I don't think this is possible only through a single SQL.

  3. #3
    Join Date
    Jan 2004


    If not, what do you recommend to do? Thanks!

  4. #4
    Join Date
    May 2002
    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??

  5. #5
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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.Type
    You can expand this to get more than three values if needed.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts