水平显示查询结果

2021-09-10 00:00:00 sql tsql sql-server

我想知道是否可以获取查询结果并将它们作为 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)

编辑以解决过快阅读问题的问题！

相关文章