在分区上合并列中的值 [英] Coalescing values in a column over a partition

问题描述

我选择通过一个例子来问这个问题，因为我认为它最清楚地说明了我想要做什么.

I have chosen to ask this question via an example as I think it most clearly illustrates what I am trying to do.

假设我有下表:

member   number  time
------   -----   -----
  1        2     19:21 
  1        4     19:24 
  1       27     19:37
  2        4     19:01
  2        7     21:56
  2        8     22:00
  2        21    22:01

如何获取以下列?

member   number  new column
------   -----   ---------
  1        2       2.4.27 
  1        4       2.4.27
  1       27       2.4.27
  2        4       4.7.8.21
  2        7       4.7.8.21
  2        8       4.7.8.21
  2       21       4.7.8.21

我正在使用 DB2 SQL.

I am using DB2 SQL.

每个成员的行数不一定相同.

There is not necessarily the same number of rows for each member.

顺序由时间决定.

推荐答案

我知道回答你自己的问题是不好的形式，但我发现了这个有用的页面:

I know it's bad form answering your own question but I have found this useful page:

https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4DB2LUW/entry/aggregating_strings42?lang=en

修改那里的代码给出:

create table test (member int, number int, time_stamp time)`;

insert into test values 
(1,2,'19:21'),
(1,4,'19:24'),
(1,27,'19:37'),
(2,4,'19:01'),
(2,7,'21:56'),
(2,8,'22:00'),
(2,21,'22:01');

select 
  member, substr(xmlcast(xmlgroup('.' || number as a order by time_stamp) as varchar(60)), 2)
from test
group by member

这篇关于在分区上合并列中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！