SQL组通过concat [英] SQL Group by with concat
问题描述
Hi
有人可以帮我解决以下问题。我需要编写一个MS SQL语句来实现以下功能:
Table1有2列: Column1
和 Column2
table1中的数据看起来像
Column1 Column2
1 a
1 b
1 c
2 w
2 e
3 x
我需要我的Sql语句输出如下
Column1 Column2
1 a,b,c
2 w,e
3 x
换句话说,我需要按column1进行分组,并将column2值连接在一起,并用逗号分隔。请注意,这需要能够在SQL Server 2000及更高版本上运行
您可以创建一个函数来连接值
创建函数dbo.concatTable1(@ column1 int)返回varchar(8000)
as
begin
declare @output varchar(8000)
选择@output = coalesce(@output +',','')+ column2
from table1
where column1 = @ column1 and column2> ; ''
return @output
end
GO
你有这张表
create table table1(column1 int,column2 varchar(10))
insert table1 select 1, 'a'
insert table1 select 1,'b'
insert table1 select 1,'c'
insert table1 select 2,'w'
insert table1 select 2,'e' '
insert table1 select 3,'x'
GO
您使用它像这样
select column1,dbo.concatTable1(column1)column2
from table1
group by column1
Hi Can anybody help me with the following. I need to write a MS SQL statment to achive the following:
Table1 has 2 columns: Column1
and Column2
Data in table1 looks like
Column1 Column2
1 a
1 b
1 c
2 w
2 e
3 x
I need my Sql statment to output as following
Column1 Column2
1 a, b, c
2 w, e
3 x
So in other words I need to group by column1 and have the column2 values concatenate with comma seperated. Please note this will need to be able to run on SQL Server 2000 and above
You can create a function to concat the values
create function dbo.concatTable1(@column1 int) returns varchar(8000)
as
begin
declare @output varchar(8000)
select @output = coalesce(@output + ', ', '') + column2
from table1
where column1 = @column1 and column2 > ''
return @output
end
GO
So assuming you had this table
create table table1 (column1 int, column2 varchar(10))
insert table1 select 1, 'a'
insert table1 select 1, 'b'
insert table1 select 1, 'c'
insert table1 select 2, 'w'
insert table1 select 2, 'e'
insert table1 select 3, 'x'
GO
You use it like this
select column1, dbo.concatTable1(column1) column2
from table1
group by column1
这篇关于SQL组通过concat的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!