连接select语句中的行

I have a select statement which is something like this

我有一个select语句,就像这样

select col1 from table1 where cond1=true

This returns results like

这会返回结果

col1
_____
5
5
6
3

but I want to change it to return me

但我想改变它来回报我

5-5-6-3

Is this possible? If so how? Please let me know how to do this. Thanks in advance for your replies.

这可能吗?如果是这样的话?请让我知道如何做到这一点。提前感谢您的回复。

8 个解决方案

#1

You probably could, but why not do this in the application? I don't know what language you're using, but you could do something like this after you get the result of the query (pseudocode):

你可能会,但为什么不在应用程序中这样做?我不知道你正在使用什么语言,但是在得到查询结果(伪代码)后你可以做这样的事情:

result = result.join('-')

#2

This does exactly what you want:

这完全符合您的要求:

declare @ret varchar(500)
set @ret=''
select @ret = @ret + col1 + '-' from test

select substring(@ret, 1, len(@ret)-1)

NOTE: if your col1 is int then I think you will have to use convert or cast to make it varchar.

注意:如果你的col1是int,那么我认为你将不得不使用convert或cast来使它成为varchar。

#3

http://blog.shlomoid.com/2008/11/emulating-mysqls-groupconcat-function.html

SELECT my_column AS [text()]
FROM   my_table
FOR XML PATH('')

#4

If you're on Microsoft SQL Server, you should be able to do it with the REPLACE command.

如果您使用的是Microsoft SQL Server,则应该能够使用REPLACE命令执行此操作。

SELECT col1,
       dynamic_text = REPLACE( 
                ( 
                    SELECT 
                        DISTINCT phone_col2 AS [data()] 
                    FROM 
                        table2
                    WHERE 
                        condition 
                    FOR XML PATH ('') 
                ), '', '-')

 FROM table1
WHERE condition

Something like that would probably work.

这样的事可能会奏效。

#5

I'd suggest you do this in front end, for a large number of rows this will be quite slow. SQL Server is extremely slow sting concatenation. You could create a .net function that takes the table and returns the string, that way you can ensure speed.

我建议你在前端执行此操作,对于大量行,这将非常慢。 SQL Server连接速度非常慢。你可以创建一个.net函数来获取表并返回字符串,这样你就可以确保速度。

If there is no performance worry, like its a one time thing etc go with the other solutions above as those are handled in sql

如果没有性能担心,就像它的一次性事情等,请使用上面的其他解决方案,因为这些是在sql中处理的

#6

This works as well:

这也有效:

  --Sample Data
DECLARE @Test TABLE
    (
    Col1 int 
    )

INSERT @Test
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5

--The query
SELECT          SUBSTRING(
                (
                SELECT          '-' + CAST(Col1 AS varchar(10))
                FROM            @Test
                ORDER BY        Col1
                FOR XML PATH    ('')
                ), 2, 2000) AS MyJoinedColumn

#7

try this:

declare @Test table (rowid int identity(1,1) primary key, col1 int not null)
insert into @test Values (5)
insert into @test Values (5)
insert into @test Values (6)
insert into @test Values (3)


declare @value varchar(500)
set @value=NULL
select @value = COALESCE(@Value+'-'+CONVERT(varchar,col1),convert(varchar,col1)) from @Test

print @value

#8

Under MySQL you can do that easily

在MySQL下你可以轻松地做到这一点

select GROUP_CONCAT(col1 SEPARATOR '-') from table1 where cond1=true

#1