汇总Oracle SQL语句中的行 [英] Agregate rows in Oracle SQL statement

问题描述

我有一个以此方式存储的项目表:

I have a table of items stored this way :

A1 | B1
A1 | B2
A1 | B3
A2 | B1
A2 | B4
...

我需要使用SQL查询进行检索:

And I need to retrieve with a SQL Query :

A1 | B1, B2, B3
A2 | B1, B4
...

推荐答案

如果您拥有11g第2版，则可以使用

If you have 11g Release 2 you can use Listagg:

Select a, Listagg(b, ', ') Within Group ( Order By b )
From t
Group By a

它允许对您的值进行排序，并且它已经随Oracle一起提供了:

It allows to sort your values, and it already comes with Oracle:

A1  B1, B2, B3
A2  B1, B4

---

否则，您可以通过汤姆·凯特(Tom Kyte)使用stragg函数，如

---

Otherwise you can use the stragg function by Tom Kyte, described in Rows to String.

Select a, stragg(b)
From t
Group By a

返回

A1  B1,B3,B2
A2  B1,B4

这篇关于汇总Oracle SQL语句中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！