Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Sydney, Australia.
    Posts
    1

    Question Unanswered: Concatenating SQL query results on one line.

    Guys,
    Here is an example.
    I have a table with hors_id, hors_name.
    Another table has hors_id, owner_name.
    That second table contains multiple entries for that hors_id, as there can be multiple owners.
    How do I construct a query that would return the following info on one line:
    hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
    The logic for the solution seems as follows:
    Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
    Any suggestions of a generic code to implement?
    I tried different coding, so far doesn't work.
    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Concatenating SQL query results on one line.

    Originally posted by bigfootguy
    Guys,
    Here is an example.
    I have a table with hors_id, hors_name.
    Another table has hors_id, owner_name.
    That second table contains multiple entries for that hors_id, as there can be multiple owners.
    How do I construct a query that would return the following info on one line:
    hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
    The logic for the solution seems as follows:
    Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
    Any suggestions of a generic code to implement?
    I tried different coding, so far doesn't work.
    Thanks.
    Hi BigFoot,

    Since SQL Server does not support Cross-Tab constructs, you will have to do some more work. I worked out a solution, but for the Customers and Orders table in the NorthWind demo database; so please translate my answer into your problem.

    First of all, you have to know, how much Orders you may expect at least. You can query the actual maximum by

    SELECT MAX(Num)
    FROM (SELECT COUNT(*) AS Num, CustomerID
    FROM Orders
    GROUP BY CustomerID) T

    As much orders you expect, as much views you have to create:

    1) CREATE VIEW Order1 AS SELECT MAX(orderid) AS ID, customerid FROM Orders GROUP BY customerid

    2) CREATE VIEW Orders2 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders1 WHERE orders.CustomerID = Orders1.CustomerID AND Orders.OrderID < Orders1.ID GROUP BY orders.CustomerID

    3) CREATE VIEW Orders3 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders2 O WHERE orders.CustomerID = O.CustomerID AND Orders.OrderID < O.ID GROUP BY orders.CustomerID

    Got the point? Select one or no order by customer per view, excluding the orders already selected in earlier views.


    Having created those views, you may select you required result as :

    SELECT C.CustomerID, C.CompanyName, O1.ID AS Order1,
    O2.ID AS Order2, O3.ID AS Order3
    FROM Customers C LEFT OUTER JOIN
    (Orders1 O1 LEFT OUTER JOIN
    (Orders2 O2 LEFT OUTER JOIN
    Orders3 O3 ON O2.CustomerID = O3.CustomerID) ON
    O1.CustomerID = O2.CustomerID) ON
    C.CustomerID = O1.CustomerID



    This works fine if your expected number of orders can be limited. If not, you will have to write a stored procedure returning your recordset.


    Cheers
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If your hors_owners table has some field for categoring the owners (say, owner_type) for each hors, you can write a cross-tab query that will place each owner_type in its own column. You could use any type description you want, as long as each hors has at most one of each type. For example, "Primary_Owner", "Secondary_Owner", "Investor", or even an ID like "1", "2", "3"... If you search books on-line for "Crosstab", they show a good example of how to write such a query. If you still have problems, post them to the forum.

    If you can't create an owner_type field, well that that is a "hors of a different color". (I couldn't resist...). This is one of those rare situations where I would recommend using a cursor, because you won't need to hard-code the number of owners. If you aren't returning hundreds or thousands of hors records, then consider putting the cursor logic in a user-defined function named something like "udf_HorsOwner_String". Your end-query could then be as simple as:

    Select *, dbo.udf_HorsOwnerString(hors_id) from tbl_hors

    If you need more guidance, post again when you have an idea of what direction you want to take with this.

    blindman

Posting Permissions

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