SQL Server的分组字符串聚合/ LISTAGG [英] Grouped string aggregation / LISTAGG for SQL Server

问题描述

我确定有人问过这个问题，但我找不到合适的搜索词。

I'm sure this has been asked but I can't quite find the right search terms.

给出这样的模式：

| 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

在以下情况下，我如何代替SQL Server中的字符串的 AGG

What do I do in place of 'AGG' for strings in SQL Server in the following style query?

SELECT *, 
 (SELECT AGG(CarModel) 
  FROM CarModels model
  WHERE model.CarMakeID = make.CarMakeID
  GROUP BY make.CarMakeID) as CarMakes
FROM CarMakes make

推荐答案

> http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

在Transact SQL中，这是一个有趣的问题，为此，存在许多解决方案和大量争论。您如何生成摘要结果，其中在汇总列中列出了每个特定类别中每一行的区别列？令人惊讶地难以实现一种简单而直观的数据显示方式。 Anith Sen总结了不同的方法，并为您选择的方法提供了警告。

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...

这篇关于SQL Server的分组字符串聚合/ LISTAGG的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！