問題描述
我想知道是否可以獲取查詢結果并將它們作為 CSV 字符串而不是作為一列單元格返回.
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.
基本上，我們有一個名為Customers 的表，還有一個名為CustomerTypeLines 的表，每個Customer 可以有多個CustomerTypeLines.當我針對它運行查詢時，當我想檢查多種類型時會遇到問題，例如:
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.
為了使它工作，我必須向客戶添加一個名為 CustomerTypes 的字段，它看起來像 ,14,66,67, 這樣我就可以做一個 Where a.CustomerTypes like'%,14,%' 和 a.CustomerTypes 像 '%,66,%' 返回 85 行.
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.
當然這很痛苦，因為每次更改 CustomerTypeLines 表時，我都必須讓我的程序為該客戶重建此字段.
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.
如果我可以在我的 where 中做一個子查詢來為我做這項工作，那就太好了，而不是像這樣返回結果:
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
它會像 ,14,66,67,
這可能嗎?
推薦答案
您將在以逗號分隔的列表上執行 LIKE 查詢時遇到各種問題.我知道，我去過那里.
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.
例如，如果您搜索 '%,14,%'，如果 14 是列表中的第一項或最后一項會怎樣?(我意識到您指定了額外的前導和尾隨逗號，但 COALESCE 方法不提供這些.)
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.)
這個怎么樣:
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)
編輯以解決過快閱讀問題的問題！
這篇關于水平顯示查詢結果的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！