SQL DB2 逗号分隔的组输出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7188542/
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
DB2 Comma Separated Output by Groups
提问by gaurav
Is there a built in function for comma separated column values in DB2 SQL?
是否有用于逗号分隔列值的内置函数DB2 SQL？
Example:If there are columns with an IDand it has 3 rows with the same IDbut have three different roles, the data should be concatenated with a comma.
示例：如果有带an 的列，ID并且有3 行相同ID但具有三个不同角色，则数据应使用逗号连接。
ID | Role
------------
4555 | 2
4555 | 3
4555 | 4
The output should look like the following, per row:
每行的输出应如下所示：
4555 2,3,4
4555 2,3,4
回答by Fernando
LISTAGGfunction is new function in DB2 LUW 9.7
LISTAGG函数是 DB2 LUW 9.7 中的新函数
see example:
见示例：
create table myTable (id int, category int);
insert into myTable values (1, 1);
insert into myTable values (2, 2);
insert into myTable values (5, 1);
insert into myTable values (3, 1);
insert into myTable values (4, 2);
example: select without any order in grouped column
示例：在分组列中无任何顺序地选择
select category, LISTAGG(id, ', ') as ids from myTable group by category;
result:
结果：
CATEGORY IDS
--------- -----
1 1, 5, 3
2 2, 4
example: select with order by clause in grouped column
示例：在分组列中使用 order by 子句进行选择
select
category,
LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids
from myTable
group by category;
result:
结果：
CATEGORY IDS
--------- -----
1 1, 3, 5
2 2, 4
回答by Nicki Voutou
I think with this smaller query, you can do what you want. This is equivalent of MySQL's GROUP_CONCAT in DB2.
我认为通过这个较小的查询，你可以做你想做的。这相当于 DB2 中 MySQL 的 GROUP_CONCAT。
SELECT
NUM,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT( ', ',ROLES))) as VARCHAR(1024)), 3) as ROLES
FROM mytable
GROUP BY NUM;
This will output something like:
这将输出如下内容：
NUM ROLES
---- -------------
1 111, 333, 555
2 222, 444
assumming your original result was something like that:
假设你的原始结果是这样的：
NUM ROLES
---- ---------
1 111
2 222
1 333
2 444
1 555
回答by boes
Depending of the DB2 version you have, you can use XML functions to achieve this.
根据您拥有的 DB2 版本，您可以使用 XML 函数来实现这一点。
Example table with some data
包含一些数据的示例表
create table myTable (id int, category int);
insert into myTable values (1, 1);
insert into myTable values (2, 2);
insert into myTable values (3, 1);
insert into myTable values (4, 2);
insert into myTable values (5, 1);
Aggregate results using xml functions
使用 xml 函数聚合结果
select category,
xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id) ) as varchar(1000)) as ids
from myTable
group by category;
results:
结果：
CATEGORY IDS
-------- ------------------------
1 <x>1</x><x>3</x><x>5</x>
2 <x>2</x><x>4</x>
Use replace to make the result look better
使用替换使结果看起来更好
select category,
replace(
replace(
replace(
xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id) ) as varchar(1000))
, '</x><x>', ',')
, '<x>', '')
, '</x>', '') as ids
from myTable
group by category;
Cleaned result
清理结果
CATEGORY IDS
-------- -----
1 1,3,5
2 2,4
Just saw a better solution using XMLTEXT instead of XMLELEMENT here.
刚看到使用XMLELEMENT的XMLTEXT而不是一个更好的解决方案在这里。
回答by Gomez NL
Since DB2 9.7.5 there is a function for that:
从 DB2 9.7.5 开始，有一个函数：
LISTAGG(colname, separator)
LISTAGG(colname, separator)
check this for more information: Using LISTAGG to Turn Rows of Data into a Comma Separated List
检查此以获取更多信息：使用 LISTAGG 将数据行转换为逗号分隔列表
回答by Bhavesh
My problem was to transpose row fields(CLOB) to column(VARCHAR) with a CSV and use the transposed table for reporting. Because transposing on report layer slows down the report.
我的问题是使用 CSV 将行字段 (CLOB) 转置为列 (VARCHAR) 并使用转置表进行报告。因为在报告层上转置会减慢报告的速度。
One way to go is to use recursive SQL. You can find many articles about that but its difficult and resource consuming if you want to join all your recursive transposed columns.
一种方法是使用递归 SQL。你可以找到很多关于这方面的文章，但如果你想加入你所有的递归转置列，它既困难又消耗资源。
I created multiple global temp tables where I stored single transposed columns with one key identifier. Eventually, I had 6 temp tables for joining 6 columns but due to limited resource allocation I wasnt able to bring all columns together. I opted to below 3 formulas and then I just had to run 1 query which gave me output in 10 seconds.
我创建了多个全局临时表，其中存储了具有一个键标识符的单个转置列。最终，我有 6 个临时表用于连接 6 列，但由于资源分配有限，我无法将所有列放在一起。我选择了以下 3 个公式，然后我只需要运行 1 个查询即可在 10 秒内给出输出。
I found various articles on using XML2CLOB functions and have found 3 different ways.
我找到了各种关于使用 XML2CLOB 函数的文章，并找到了 3 种不同的方法。
REPLACE(VARCHAR(XML2CLOB(XMLAGG(XMLELEMENT(NAME "A",ALIASNAME.ATTRIBUTENAME)))),'', ',') AS TRANSPOSED_OUTPUT
NVL(TRIM(',' FROM REPLACE(REPLACE(REPLACE(CAST(XML2CLOB(XMLAGG(XMLELEMENT(NAME "E", ALIASNAME.ATTRIBUTENAME))) AS VARCHAR(100)),'',' '),'',','), '', 'Nothing')), 'Nothing') as TRANSPOSED_OUTPUT
RTRIM(REPLACE(REPLACE(REPLACE(VARCHAR(XMLSERIALIZE(XMLAGG(XMLELEMENT(NAME "A",ALIASNAME.ATTRIBUTENAME) ORDER BY ALIASNAME.ATTRIBUTENAME) AS CLOB)), '',','),'',''),'','')) AS TRANSPOSED_OUTPUT
Make sure you are casting your "ATTRIBUTENAME" to varchar in a subquery and then calling it here.
确保在子查询中将“ATTRIBUTENAME”转换为 varchar，然后在此处调用它。
回答by Esperento57
other possibility, with recursive cte
其他可能性，使用递归 cte
with tablewithrank as (
select id, category, rownumber() over(partition by category order by id) as rangid , (select count(*) from myTable f2 where f1.category=f2.category) nbidbycategory
from myTable f1
),
cte (id, category, rangid, nbidbycategory, rangconcat) as (
select id, category, rangid, nbidbycategory, cast(id as varchar(500)) from tablewithrank where rangid=1
union all
select f2.id, f2.category, f2.rangid, f2.nbidbycategory, cast(f1.rangconcat as varchar(500)) || ',' || cast(f2.id as varchar(500)) from cte f1 inner join tablewithrank f2 on f1.rangid=f2.rangid -1 and f1.category=f2.category
)
select category, rangconcat as IDS from cte
where rangid=nbidbycategory
回答by Sam Casil
Try this:
尝试这个：
SELECT GROUP_CONCAT( field1, field2, field3 ,field4 SEPARATOR ', ')
