DB2逗号分隔输出
如何在DB2中的sql 中为逗号分隔列值获取内置函数,例如,如果存在具有此策略ID的列,并且它具有3行具有相同ID但在三行中具有三个不同角色的行,那么它应该检索一行中的行“3,4,5”DB2逗号分隔输出
例如
1. 4555 "2"
2. 4555 "3"
3. 4555 "4"
输出4555 2,3,4
在DB2一行
这是你需要的东西:
http://radheshk.blogspot.com/2008/02/sql-tips-techniques-string-aggregation.html
可悲的是,没有在DB2中轻松定义自定义集合函数的方法(或不是我知道的帽子)。所以我们必须求助于递归查询,就像上面的例子。
另一种方式来解决这个问题涉及光标,但可能是更糟......
试试这个:
SELECT GROUP_CONCAT(field1, field2, field3 ,field4 SEPARATOR ', ')
会出现什么这里是field1 – gaurav
Field1:1.4555 - SELECT GROUP_CONCAT(field1,,field3,field4 SEPARATOR',') –
Ca ñ你请建立你的问题?:D –
取决于你有,你可以使用XML函数来实现这一目标的DB2版本。
示例表的一些数据
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);
使用XML功能
select category,
xmlserialize(XMLAGG(XMLELEMENT(NAME "x", id)) as varchar(1000)) as ids
from myTable
group by category;
结果汇总结果:
CATEGORY IDS
-------- ------------------------
1 <x>1</x><x>3</x><x>5</x>
2 <x>2</x><x>4</x>
使用替换,使结果更好看
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;
清理结果
CATEGORY IDS
-------- -----
1 1,3,5
2 2,4
刚看到使用XMLTEXT代替XMLELEMENT here一个更好的解决方案。
LISTAGG函数是DB2 LUW 9中的新函数。7
见例如:
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);
例如:在分组列没有任何顺序选择
select category, LISTAGG(id, ', ') as ids from myTable group by category;
结果:
CATEGORY IDS
--------- -----
1 1, 5, 3
2 2, 4
例如:在分组列由子句选择具有顺序
select
category,
LISTAGG(id, ', ') WITHIN GROUP(ORDER BY id ASC) as ids
from myTable
group by category;
结果:
CATEGORY IDS
--------- -----
1 1, 3, 5
2 2, 4
我觉得这个更小的查询,你可以做你想做的。 这相当于DB2中MySQL的GROUP_CONCAT。
SELECT
NUM,
SUBSTR(xmlserialize(xmlagg(xmltext(CONCAT(', ',ROLES))) as VARCHAR(1024)), 3) as ROLES
FROM mytable
GROUP BY NUM;
这将输出类似:
NUM ROLES
---- -------------
1 111, 333, 555
2 222, 444
assumming原来的结果是类似的东西:
NUM ROLES
---- ---------
1 111
2 222
1 333
2 444
1 555
我的问题是要转的行字段(CLOB)列(VARCHAR )与CSV并使用转置表进行报告。因为在报告层上转置会减慢报告速度。
一种方法是使用递归SQL。你可以找到很多关于这方面的文章,但是如果你想加入你所有的递归转置列,那么它的困难和资源消耗。
我创建了多个全局临时表,其中存储了具有一个关键标识符的单个转置列。最终,我有6个临时表加入6列,但由于资源分配有限,我无法将所有列结合在一起。我选择了3个以下的公式,然后我只需要运行1个查询就可以在10秒内输出结果。
我发现了有关使用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
确保您铸造你的 “ATTRIBUTENAME” 为varchar在子查询中,然后在这里调用它。
另一种可能性,递归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
由于DB2 9.7.5存在的一个功能:
LISTAGG(colname, separator)
检查这个以了解更多信息:Using LISTAGG to Turn Rows of Data into a Comma Separated List
感谢您的答案 – gaurav