如何在 T-SQL 中将多行作为单行返回? [英] How can I return multiple rows as a single row in T-SQL?

问题描述

几个月前，我们的供应商向我们的票务系统添加了一项功能，让我们可以向票务添加任意数量的自定义字段.为了报告目的，我想将这些字段与其他调用信息一起查询出来，但每个可扩展字段都作为一行存储在数据库中.所以基本上你有这样的事情:

A few months ago our vendor added a capability to our ticketing system which lets us add any number of custom fields to a ticket. I'd like to query these fields out along with the other call information for reporting purposes, but each extensible field is stored as a row in the database. So basically you have something like this:

ext_doc_no    call_record    value
1             1001           Test
2             1001           test2
3             1001           moretest

我想回查询:

1001    Test    test2    moretest

我曾尝试使用 PIVOT，但这对使用聚合函数之类的东西要求很高.关于如何做到这一点的任何其他想法?

I've tried to use PIVOT, but that's rather demanding about things like using an aggregate function. Any other ideas on how to do this?

我还尝试将每一行分别查询到主查询中，并使用一个函数……但这两种方法都太慢了.我需要一些东西来一次获取所有行，对它们进行 PIVOT，然后加入主查询.

I also tried querying each row separately into the main query, and using a function... but both methods are way too slow. I need something to get all the rows at once, PIVOT them and then join into the main query.

推荐答案

试试看 这个答案.

它完全符合您的要求.

这篇关于如何在 T-SQL 中将多行作为单行返回?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！