Displaying Query Results Horizontally
- by AndyD273
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?
