SQL Server 的分组字符串聚合/LISTAGG
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3368942/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grouped string aggregation / LISTAGG for SQL Server
提问by Matt Mitchell
I'm sure this has been asked but I can't quite find the right search terms.
我确定有人问过这个问题，但我无法找到正确的搜索词。
Given a schema like this:
给定这样的架构：
| CarMakeID | CarMake
------------------------
| 1 | SuperCars
| 2 | MehCars
| CarMakeID | CarModelID | CarModel
-----------------------------------------
| 1 | 1 | Zoom
| 2 | 1 | Wow
| 3 | 1 | Awesome
| 4 | 2 | Mediocrity
| 5 | 2 | YoureSettling
I want to produce a dataset like this:
我想生成这样的数据集：
| CarMakeID | CarMake | CarModels
---------------------------------------------
| 1 | SuperCars | Zoom, Wow, Awesome
| 2 | MehCars | Mediocrity, YoureSettling
What do I do in place of 'AGG' for strings in SQL Server in the following style query?
在以下样式查询中，我如何代替 SQL Server 中的字符串的“AGG”？
SELECT *,
(SELECT AGG(CarModel)
FROM CarModels model
WHERE model.CarMakeID = make.CarMakeID
GROUP BY make.CarMakeID) as CarMakes
FROM CarMakes make
采纳答案by gbn
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
It is an interesting problem in Transact SQL, for which there are a number of solutions and considerable debate. How do you go about producing a summary result in which a distinguishing column from each row in each particular category is listed in a 'aggregate' column? A simple, and intuitive way of displaying data is surprisingly difficult to achieve. Anith Sen gives a summary of different ways, and offers words of caution over the one you choose...
这是 Transact SQL 中的一个有趣问题，对此有许多解决方案和相当多的争论。您如何生成汇总结果，其中每个特定类别中每一行的区别列都列在“聚合”列中？一种简单、直观的数据显示方式出乎意料地难以实现。Anith Sen 总结了不同的方式，并对您选择的方式提出警告......
回答by Kannan Kandasamy
If it is SQL Server 2017 or SQL Server VNext, Azure SQL database you can use String_agg as below:
如果是 SQL Server 2017 或 SQL Server VNext，Azure SQL 数据库你可以使用 String_agg 如下：
SELECT make.CarMakeId, make.CarMake,
CarModels = string_agg(model.CarModel, ', ')
FROM CarModels model
INNER JOIN CarMakes make
ON model.CarMakeId = make.CarMakeId
GROUP BY make.CarMakeId, make.CarMake
Output:
输出：
+-----------+-----------+---------------------------+
| CarMakeId | CarMake | CarModels |
+-----------+-----------+---------------------------+
| 1 | SuperCars | Zoom, Wow, Awesome |
| 2 | MehCars | Mediocrity, YoureSettling |
+-----------+-----------+---------------------------+
