水平显示查询结果

Displaying Query Results Horizontally(水平显示查询结果)
本文介绍了水平显示查询结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

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

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

这篇关于水平显示查询结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

相关文档推荐

Query with t(n) and multiple cross joins(使用 t(n) 和多个交叉连接进行查询)
Unpacking a binary string with TSQL(使用 TSQL 解包二进制字符串)
Max rows in SQL table where PK is INT 32 when seed starts at max negative value?(当种子以最大负值开始时,SQL 表中的最大行数其中 PK 为 INT 32?)
Inner Join and Group By in SQL with out an aggregate function.(SQL 中的内部连接和分组依据,没有聚合函数.)
Add a default constraint to an existing field with values(向具有值的现有字段添加默认约束)
SQL remove from running total(SQL 从运行总数中删除)