将所有列值用sql表示。

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中有什么方法可以做到这一点吗?

12 个解决方案

#1

In SQL Server:

在SQL服务器:

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

#2

Quassnoi's Oracle solution is quite impressive, but I found simpler ones using SYS_CONNECT_BY_PATH() rather than the MODEL magic.

Quassnoi的Oracle解决方案令人印象深刻，但我发现使用SYS_CONNECT_BY_PATH()而不是使用模型魔法的更简单的解决方案。

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;

#3

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)

#4

The mysql way:

mysql道:

select group_concat(somecolumn separator '') from sometable

#5

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。

#6

Edit: Since version 8.4.0 CUBRID provides 90% compatibility with MySQL. Thus, it supports GROUP_CONCAT which has similar syntax as in MySQL:

编辑:因为8.4.0版CUBRID提供了90%的MySQL兼容性。因此，它支持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 solution supported 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，看起来要容易一点。

#7

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:

如果列已经是字符串，您不需要cast，您可以这样做:

select res1 + res2 + res3  as fooconcat from foo

For data from multiple rows, use PIVOT.

对于来自多行的数据，使用PIVOT。

#8

Concatenating strings depends on the database you are using (you havnt mentioned what version in your question so here goes)...

连接字符串取决于您正在使用的数据库(您还没有提到您的问题中的版本，所以现在开始)……

In Oracle and DB2 you can use the CONCAT function... CONCAT(string, string)

在Oracle和DB2中，您可以使用CONCAT函数……CONCAT(字符串,字符串)

SQL Server you can use the '+' operator... string1 + string2 + string3

您可以使用“+”操作符…弦1 +弦2 +弦3

In MySQL it is CONCAT(string, string... n_string)

在MySQL中是CONCAT(字符串，字符串……)n_string)

Finally in PostgreSQL it is TEXTCAT(string, string)...

最后在PostgreSQL中是TEXTCAT(string, string)……

...I got this out of this little cool book I have sitting on my desk SQL Pocket Guide from O'Reilly... check it out!

…这是我从一本很酷的书中得到的，我坐在O'Reilly的SQL袖珍指南上。点击这里查看详情！

#9

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;

#10

SQL Server 2008 R2:

declare @ColumnNameList VARCHAR(MAX)


 SELECT @ColumnNameList  = COALESCE(@ColumnNameList +',' ,'') + ColumnName 
                     FROM 
                       <<table name>>

select @ColumnNameList

#11

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

这应该解决

#12

-2

Select ([col1] +','+[col2]+','+ [col3]+','+[col4]) as [MyCol] From [Table]

选择([col1][col2]+ +”、“+”、“+(col3)+”、“+[col4])的[MyCol](表)

#1