SQL 连接sql中的所有列值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/970481/
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
Concat all column values in sql
提问by paweloque
How to concat all column values from differenct rows returned from a sql query into one value? This is an example:
如何将从 sql 查询返回的不同行中的所有列值连接为一个值？这是一个例子：
a query returns:
查询返回：
FOO ------ RES1 RES2 RES3
now I want to have a result like the following one:
现在我想得到如下结果：
FOOCONCAT ----- RES1RES2RES3
Are there any ways to do this in sql?
有没有办法在sql中做到这一点？
回答by Quassnoi
In SQL Server:
在SQL Server：
SELECT col1 AS [text()]
FROM foo
FOR XML PATH ('')
In MySQL:
在MySQL：
SELECT GROUP_CONCAT(col1 SEPARATOR '')
FROM foo
In PostgreSQL:
在PostgreSQL：
SELECT array_to_string
(
ARRAY
(
SELECT col1
FROM foo
), ''
)
In Oracle:
在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
回答by devio
Quassnoi's Oracle solution is quite impressive, but I foundsimplerones using SYS_CONNECT_BY_PATH() rather than the MODEL magic.
Quassnoi 的 Oracle 解决方案令人印象深刻，但我发现使用 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;
回答by ???u
The mysql way:
mysql的方式：
select group_concat(somecolumn separator '') from sometable
回答by John McLusky
Assuming that it's one column with multiple values, this approach works for MS SQL Server (I can't speak for other systems).
假设它是具有多个值的一列，这种方法适用于 MS SQL Server（我不能说其他系统）。
declare @result varchar(max)
set @result = ''
select @result = @result + RES
from (query goes here)
回答by Steve Broberg
Here is the answer you are looking for; I had a feeling the solution lay in the CONNECT BY operation, I just hadn't used the SYS_CONNECT_BY_PATH pseudocolumn before (which displays the full path to the node in a tree, separating node names by a "/"). Assuming that your set of "foo" values before are multiple rows in a table, grouped by a column "myKey", e.g.:
这是您正在寻找的答案；我觉得解决方案在于 CONNECT BY 操作，我之前没有使用 SYS_CONNECT_BY_PATH 伪列（它显示树中节点的完整路径，用“/”分隔节点名称）。假设您之前的“foo”值集是表中的多行，按“myKey”列分组，例如：
myKey foo
-------- ----------
group 1 apple
group 1 orange
group 1 pear
group 2 ape
group 2 bear
group 2 kitten
you can treat the data as if it were a tree schema, and pretend that the values of each group represent nodes going down a branch. In that case, you'd do this:
您可以将数据视为树模式，并假设每个组的值代表沿着分支向下的节点。在这种情况下，你会这样做：
SELECT myKey
, SUBSTR(MAX(REPLACE(SYS_CONNECT_BY_PATH(foo, '/')
,'/'
,' '
)
)
,2
) FooConcat
FROM ( SELECT MyKey
, Foo
, row_number() OVER (Partition by myKey order by myKey) NodeDepth
FROM MyTable
)
START WITH NodeDepth = 1
CONNECT BY PRIOR myKey = myKey
AND PRIOR NodeDepth = NodeDepth -1
GROUP BY myKey
;
Of course, the order of the concatenated values would be random; if your table had another column ("bar") that you could use as an ordering field that was ascending and contiguous, you could dispense with the subquery (which only exists to put an imaginary depth to the tree) and use the table directly, replacing NodeDepth with bar.
当然，串联值的顺序是随机的；如果您的表有另一列（“bar”），您可以将其用作升序和连续的排序字段，则可以省去子查询（它只存在用于将假想深度放入树中）并直接使用表，用 bar 替换 NodeDepth。
回答by Eye
Edit:Since version 8.4.0 CUBRID provides 90% compatibilitywith MySQL. Thus, it supports GROUP_CONCATwhich has similar syntax as in MySQL:
编辑：由于版本 8.4.0 CUBRID 提供了与 MySQL 的90% 兼容性。因此，它支持GROUP_CONCAT，它的语法与 MySQL 相似：
CREATE TABLE t(i int);
INSERT INTO t VALUES (4),(2),(3),(6),(1),(5);
SELECT GROUP_CONCAT(i*2+1 ORDER BY 1 SEPARATOR '') FROM t;
group_concat(i*2+1 order by 1 separator '')
======================
'35791113'
Quite powerful, isn't it? And below is an alternative solutionsupported natively in CUBRID.
相当强大，不是吗？下面是CUBRID 中原生支持的替代解决方案。
SELECT MAX(SYS_CONNECT_BY_PATH(s_name, '')) AS conc_s_name
FROM (
SELECT ROWNUM AS r, s_name FROM code
) AS res
START WITH r = 1
CONNECT BY PRIOR r = r - 1;
It's so interesting that this way of concatenating different row column values in CUBRID is almost identical to Oracle's way as provided by @devio. In CUBRID it looks a little bit easier though.
有趣的是，这种在 CUBRID 中连接不同行列值的方式几乎与 @devio 提供的 Oracle 方式相同。在 CUBRID 中，它看起来更容易一些。
回答by Kristy Welsh
SQL Server 2008 R2:
SQL Server 2008 R2：
declare @ColumnNameList VARCHAR(MAX)
SELECT @ColumnNameList = COALESCE(@ColumnNameList +',' ,'') + ColumnName
FROM
<<table name>>
select @ColumnNameList
回答by navya
I found the answer on How to concatenate all the records in a column returned by a query into one varchar string in T-SQL?
我找到了关于如何将查询返回的列中的所有记录连接到 T-SQL 中的一个 varchar 字符串的答案？
declare @s varchar(8000)
select @s = coalesce(@s + col, col) from tbl
This should solve
这应该解决
回答by Steve Broberg
It might not be what you're looking for, but I've had good luck in the past with constructions like this:
它可能不是您要找的东西，但我过去曾用这样的结构祝您好运：
SELECT MAX(DECODE(fookey, 1, foo, NULL))
|| MAX(DECODE(fookey, 2, foo, NULL))
|| MAX(DECODE(fookey, 3, foo, NULL))
|| MAX(DECODE(fookey, 4, foo, NULL))
, groupingvalue
FROM mytable
GROUP BY groupingvalue;
It's platform independent, and it works well when you have a arbitrary, but limited number of values for foo, and they're based on some other key value. For example, if you have a table of invoices, and you want to see all the line times from the invoice on a single row, concatenated, and you have an upper limit of 5 line items, it would look like this:
它是独立于平台的，当您拥有任意但数量有限的 foo 值并且它们基于其他一些键值时，它可以很好地工作。例如，如果您有一个发票表，并且您想在单个行中查看发票中的所有行时间，并且您有 5 个行项目的上限，则如下所示：
SELECT MAX(DECODE(lineno, 1, foo, NULL))
|| ', '
|| MAX(DECODE(lineno, 2, foo, NULL))
|| ', '
|| MAX(DECODE(lineno, 3, foo, NULL))
|| ', '
|| MAX(DECODE(lineno, 4, foo, NULL))
|| ', '
|| MAX(DECODE(lineno, 5, foo, NULL))
, invoiceid
FROM lineitem
GROUP BY invoiceid;
回答by RedFilter
select cast(res1 as varchar)+cast(res2 as varchar)+cast(res3 as varchar) as fooconcat from foo
If the columns are already strings you do not need the cast, you can just do:
如果列已经是字符串，您不需要强制转换，您可以这样做：
select res1 + res2 + res3 as fooconcat from foo
For data from multiple rows, use PIVOT.
对于来自多行的数据，请使用PIVOT。
