在SQL Server :
SELECT col1 AS [text()] FROM foo FOR XML PATH ('')
在MySQL :
SELECT GROUP_CONCAT(col1 SEPARATOR '') FROM foo
在PostgreSQL :
SELECT array_to_string ( ARRAY ( SELECT col1 FROM foo ), '' )
在Oracle :
SELECT * FROM ( SELECT col1, ROW_NUMBER() OVER(ORDER BY 1) AS rn FROM foo MODEL DIMENSION BY (rn) MEASURES (col1, col1 AS group_concat, 0 AS mark) RULES UPDATE ( group_concat[rn > 1] = group_concat[CV() - 1] || col1[CV()], mark[ANY] = PRESENTV(mark[CV() + 1], 0, 1) ) ) WHERE mark = 1
Quassnoi的Oracle解决scheme相当出色,但我发现使用SYS_CONNECT_BY_PATH()而不是MODEL魔术更简单 。
SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(foo, '/')), '/', '') conc FROM ( SELECT T_FOO.*, ROW_NUMBER() OVER (ORDER BY FOO) R FROM T_FOO ) START WITH r=1 CONNECT BY PRIOR r = r-1;
假设它是一列有多个值,这种方法适用