Displaying Query Results Horizontally

Question

I am wondering if it is possible to take the results of a query and return them as a CSV string instead of as a column of cells.

Basically, we have a table called Customers, and we have a table called CustomerTypeLines, and each Customer can have multiple CustomerTypeLines. When I run a query against it, I run into problems when I want to check multiple types, for instance:

    Select * 
      from Customers a 
Inner Join CustomerTypeLines b on a.CustomerID = b.CustomerID 
     where b.CustomerTypeID = 14 and b.CustomerTypeID = 66

...returns nothing because a customer can't have both on the same line, obviously.

In order to make it work, I had to add a field to Customers called CustomerTypes that looks like ,14,66,67, so I can do a Where a.CustomerTypes like '%,14,%' and a.CustomerTypes like '%,66,%' which returns 85 rows.

Of course this is a pain because I have to make my program rebuild this field for that Customer each time the CustomerTypeLines table is changed.

It would be nice if I could do a sub query in my where that would do the work for me, so instead of returning the results like:

14
66
67

it would return them like ,14,66,67,

Is this possible?

Answer 1

To do this without denormalising you can use something like the following to get a table of all Customers matching all the values in the IN clause that you can then join against.

SELECT CustomerId 
FROM CustomerTypes
WHERE CustomerTypeID in (14, 66)
GROUP BY CustomerId
HAVING COUNT(DISTINCT CustomerTypeID) = 2

Actually you say in your question that you already have a query that returns the results like:

14
66
67

This is the right format already for the following relational division technique.

SELECT * 
FROM Customers c
    WHERE NOT EXISTS
    (
        SELECT * FROM @YourQuery y
        WHERE NOT EXISTS
            (
            SELECT * FROM CustomerTypeLines ctl
            WHERE ctl.CustomerTypeID = y.CustomerTypeID
            AND c.CustomerID = ctl.CustomerID
            )
        )

Answer 2

I believe the technique you're looking for will make use of the COALESCE function. See http://www.4guysfromrolla.com/webtech/092105-1.shtml.

Answer 3

If you want to get all customers who have both a 14 and a 66 then you could use:

SELECT
    C.CustomerID,
    C.SomeColumn
FROM
    Customers C
WHERE
    EXISTS (SELECT * FROM CustomerTypes CT1 WHERE CT1.CustomerID = C.CustomerID AND CT1.CustomerTypeID = 14) AND
    EXISTS (SELECT * FROM CustomerTypes CT2 WHERE CT2.CustomerID = C.CustomerID AND CT2.CustomerTypeID = 66)

A more generic solution (to retrieve customers based on any number of customer type IDs would be dependent on how you are passing those IDs to SQL (for example, as a table paramater into a stored procedure).

Answer 4

You're going to run into all kinds of problems doing a LIKE query on a comma-delimited list. I know, I've been there.

For example, if you search for '%,14,%', what happens if 14 is the first or last item in the list? (I realize you specify extra leading and trailing commas, but the COALESCE method doesn't supply those.)

How about this instead:

Select * from Customers a 
Inner Join CustomerTypeLines b 
on a.CustomerID = b.CustomerID 
WHERE a.CustomerID in 
    (SELECT customerID from CustomerTypeLines
     WHERE CustomerTypeID = 14)
AND a.CustomerID in
    (SELECT customerID from CustomerTypeLines
     WHERE CustomerTypeID in 66)

Edited to fix overhasty reading of the question!

Answer 5

It's a pain because you designed it wrong.

Since the Customers have a one to many relationship with CustomerType, you should create another table to store those values instead of jamming all those values in one field. That way you can query against those values a lot easier & faster.

Then you can use the FOR XML PATH clause to delimit records by comma http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist