Display query results horizontally

Question

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

Basically, we have a Customer table, and we have a CustomerTypeLines table, and each Customer can have several CustomerTypeLines. When I run a query against it, I have problems when I want to check several types, for example:

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

... returns nothing, because the client cannot have both on the same line, obviously.

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

Of course, this is a pain, because I have to force my program to rebuild this field for this Client each time the CustomerTypeLines table is changed.

It would be nice if I could make a sub-query in my place where it would work for me, so instead of returning results, for example:

 14 66 67 

he will return them, like ,14,66,67,

Is it possible?

Answer 1

You will have trouble running a LIKE request on a comma-delimited list. I know I was there.

For example, if you search for '%,14,%' , what happens if 14 is the first or last item in the list? (I understand that you specify extra commas, but the COALESCE method does not provide them.)

How about this:

 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 correct a rash reading of a question!

Answer 2

To do this without denormalization, you can use something like the following to get a table of all Clients matching all the values ​​in the IN section that you can join with.

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

In fact, you say that you already have a query that returns results, for example:

 14 66 67 

This is the correct format already for the relational division method .

 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 3

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

Answer 4

If you want to get all clients that have both 14 and 66, you can 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 general solution (to retrieve clients based on any number of client type identifiers will depend on how you pass these SQL identifiers (for example, as a table parameter to a stored procedure).

Answer 5

This is pain because you created it wrong.

Since Customers have relationships with each other with CustomerType, you should create another table to store these values ​​instead of clogging all these values ​​in one field. Thus, you can query for these values ​​much easier and faster.

You can then use the FOR XML PATH clause to delimit the entries with a comma http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=createacommadelimitedlist