sqlserver中数据合并问题

[tangulao6575]

如图有两行数据，这两行数据除了中间的一列不一样之外，其他都一样，那如何把两行的中间那一列用逗号隔开，然后两行合并成一行？

[shoppo0505]

这个要写函数了，你看看行列转换，这里就有几十个帖子。

[tangulao6575]

引用 3 楼 ZaoLianBuXiQi 的回复:

CREATE TABLE [dbo].[Table_2](
	[b1] [nvarchar](50) NULL,
	[b2] [nvarchar](50) NULL,
	[b3] [nvarchar](50) NULL
) ON [PRIMARY]
go
insert into [Table_2]
select 'a1','20','a3' union all
select 'a1','30','a3' union all
select 'a1','40','a3'

GO
select* from [Table_2]
go
select b1,b3,stuff((select ','+ b2 from [Table_2] a where a.b1 = b.b1 and a.b3 = b.b3 for xml path('')),1,1,'')b2 from
[Table_2] b 
group by b1,b3

不是啊，我的意思是说把那张表里面相同学生名字的合并，把中间那一列用逗号隔开

[MrYangkang]

引用 4 楼 tangulao6575 的回复:

[quote=引用 3 楼 ZaoLianBuXiQi 的回复:]

CREATE TABLE [dbo].[Table_2](
	[b1] [nvarchar](50) NULL,
	[b2] [nvarchar](50) NULL,
	[b3] [nvarchar](50) NULL
) ON [PRIMARY]
go
insert into [Table_2]
select 'a1','20','a3' union all
select 'a1','30','a3' union all
select 'a1','40','a3'

GO
select* from [Table_2]
go
select b1,b3,stuff((select ','+ b2 from [Table_2] a where a.b1 = b.b1 and a.b3 = b.b3 for xml path('')),1,1,'')b2 from
[Table_2] b 
group by b1,b3

不是啊，我的意思是说把那张表里面相同学生名字的合并，把中间那一列用逗号隔开[/quote] 不知道你要的结果怎样的

[绿豆丫]

引用 4 楼 tangulao6575 的回复:

[quote=引用 3 楼 ZaoLianBuXiQi 的回复:]

CREATE TABLE [dbo].[Table_2](
	[b1] [nvarchar](50) NULL,
	[b2] [nvarchar](50) NULL,
	[b3] [nvarchar](50) NULL
) ON [PRIMARY]
go
insert into [Table_2]
select 'a1','20','a3' union all
select 'a1','30','a3' union all
select 'a1','40','a3'

GO
select* from [Table_2]
go
select b1,b3,stuff((select ','+ b2 from [Table_2] a where a.b1 = b.b1 and a.b3 = b.b3 for xml path('')),1,1,'')b2 from
[Table_2] b 
group by b1,b3

不是啊，我的意思是说把那张表里面相同学生名字的合并，把中间那一列用逗号隔开[/quote] 我是外星来的，看个热闹。

[绿豆丫]

我想你应该需要用#2的for xml path 就可以解决你的问题了

[MrYangkang]

CREATE TABLE [dbo].[Table_2](
	[b1] [nvarchar](50) NULL,
	[b2] [nvarchar](50) NULL,
	[b3] [nvarchar](50) NULL
) ON [PRIMARY]
go
insert into [Table_2]
select 'a1','20','a3' union all
select 'a1','30','a3' union all
select 'a1','40','a3'

GO
select* from [Table_2]
go
select b1,b3,stuff((select ','+ b2 from [Table_2] a where a.b1 = b.b1 and a.b3 = b.b3 for xml path('')),1,1,'')b2 from
[Table_2] b 
group by b1,b3

[dengixnyu]

CREATE TABLE [dbo].[Table_2](
	[b1] [nvarchar](50) NULL,
	[b2] [nvarchar](50) NULL,
	[b3] [nvarchar](50) NULL
) ON [PRIMARY]

GO
select* from [Table_2]

--(3 行受影响)
--a1	20	a3
--a1	20	a3
--a1	42	a3

go
SELECT B.b1,LEFT(newb2,LEN(newb2)-1) as newbb2,B.b3 FROM (
SELECT b1,b3,
(SELECT b2+',' FROM Table_2 
  WHERE b1=A.b1  and b3 = A.b3
  FOR XML PATH('')) AS newb2
FROM Table_2 A 
GROUP BY b1,b3
) B 
--(1 行受影响)
--a1	20,20,42	a3