我需要连接不同行的字符串,如下所示
每个NAME的行数是可变的,其范围是未知的。
NAME COLOR
Bob Red
Bob Blue
Tom Green
John Red
John Yellow
John Purple
期望输出:
NAME COLORS
Bob RedBlue
Tom Green
John RedYellowPurple
我面临的限制是:
- Oracle 8i(8.1.7.4.0)-我对此无法控制,升级不是一个选项
- 只读访问
- 无法创建
所以基本上:
- 没有CREATE
- 无LISTAGG
- 无XMLAGG
- 无WM_CONCAT
- 无参考光标
- 无SYS_CONNECT_BY_PATH
我是SOL吗?
Oracle 8i至少支持分析函数,因此您可以使用row_number()
或dense_rank()
为每个颜色值分配一个标称数字(按任何对您有意义的方式排序):
select name, color,
row_number() over (partition by name order by color) as rn
from your_table;
然后在手动枢轴上使用变体,每个可能的行号有一个max(decode())
:
select name,
max(decode(rn, 1, color))
|| max(decode(rn, 2, color))
|| max(decode(rn, 3, color))
|| max(decode(rn, 4, color))
|| max(decode(rn, 5, color))
|| max(decode(rn, 6, color))
|| max(decode(rn, 7, color))
-- ...
as colors
from (
select name, color,
row_number() over (partition by name order by color) as rn
from your_table
)
group by name
order by name;
NAME COLORS
---------- ----------------------------------------------------------------------
Bob BlueRed
John PurpleRedYellow
Tom Green
您说过范围是未知的,但即使每个名称的行数不受限制,您仍然会受到最终连接字符串最多必须为4000个字符的限制——根据您对实际颜色值的了解,这将为您提供最大可用数。(您可以一次性自动生成解码部分)。
如果需要,可以以相同的方式包含分隔符:
select name,
max(decode(rn, 1, color))
|| max(decode(rn, 2, ',')) || max(decode(rn, 2, color))
|| max(decode(rn, 3, ',')) || max(decode(rn, 3, color))
|| max(decode(rn, 4, ',')) || max(decode(rn, 4, color))
|| max(decode(rn, 5, ',')) || max(decode(rn, 5, color))
|| max(decode(rn, 6, ',')) || max(decode(rn, 6, color))
|| max(decode(rn, 7, ',')) || max(decode(rn, 7, color))
|| max(decode(rn, 8, ',')) || max(decode(rn, 8, color))
-- ...
as colors
from (
select name, color,
row_number() over (partition by name order by color) as rn
from your_table
where color is not null
)
group by name
order by name;
NAME COLORS
---------- ---------------------------------------------------------------------------------------
Bob Blue,Red
John Purple,Red,Yellow
Tom Green
我在内部查询中包含了一个is not null
过滤器,以防该列可以为null——这应该可以防止最终列表中出现空元素。
(没有在8i中进行测试,因为我找不到一个这么旧的实例可以旋转,但我不认为这是在使用后来引入的任何东西…)