建表: create table tb( t1 varchar(30),t2 varchar(30), t3 varchar(30),t4 varchar(30), t5 varchar(30) ) insert into tb select '网易', '张三', '139', '男', '北京' union all select '网易', '李四', '138', '男', '浙江' union all select '阿里', '王五', '139', '女', '北京' union all select '网易', '孙一', '135', '男', '浙江' go 查询:select t1,t2,t3,t4,t5 from ( select distinct t1, stuff((select ','+b.t2 from tb b where a.t1 = b.t1 for xml path('')) ,1,1,'') as t2, t3,t4,t5, ROW_NUMBER() over(partition by t1 order by t3 desc) rownum from tb a )t where rownum =1 /* t1 t2 t3 t4 t5 阿里 王五 139 女 北京 网易 张三,李四,孙一 139 男 北京 */
上面代码只适合sql server 2005及以上的版本
create table #tb(web nvarchar(10),cname nvarchar(10),telno nvarchar(20),sex nvarchar(1),area nvarchar(10)) insert into #tb select '网易','张三',139,'男','北京' union all select '网易','李四',138,'男','浙江' union all select '阿里','王五',139,'女','北京' union all select '网易','孙一',135,'男','浙江' goselect a.web,stuff((select ','+cname from #tb b where b.web=a.web --and b.cname=a.cname for xml path('')),1,1,'') 'cname' ,telno=(select top 1 telno from #tb c where a.web=c.web) ,sex=(select top 1 sex from #tb c where a.web=c.web) ,area=(select top 1 area from #tb c where a.web=c.web) from #tb a group by a.web drop table #tb/* web cname telno sex area --------------------------------------------------- 网易 张三,李四,孙一 139 男 北京 阿里 王五 139 女 北京 */
建表,外加建一个函数: create table tb( t1 varchar(30),t2 varchar(30), t3 varchar(30),t4 varchar(30), t5 varchar(30) ) insert into tb select '网易', '张三', '139', '男', '北京' union all select '网易', '李四', '138', '男', '浙江' union all select '阿里', '王五', '139', '女', '北京' union all select '网易', '孙一', '135', '男', '浙江' goif exists(select * from sys.objects where name = 'fn_mergeSTR') drop function dbo.fn_mergeSTR go create function dbo.fn_mergeSTR(@id varchar(30), @split varchar(10)) --分隔符 returns varchar(300) as begin declare @str varchar(300);
set @str = '';
--这里的code改为需要合并的字段 select @str = @str + @split + t2 from tb where t1 = @id
set @str = stuff(@str,1,1,'' )
return @str --返回值 end go 查询:if OBJECT_ID('tempdb..#temp') is not null drop table #temp goselect identity(int,1,1) id,t1,dbo.fn_mergeSTR(t1,',') t2,t3,t4,t5 into #temp from tb select t1,t2,t3,t4,t5 from ( select *, (select COUNT(*) from #temp b where a.id >= b.id and a.t1 = b.t1) rownum from #temp a )t where rownum = 1 /* t1 t2 t3 t4 t5 网易 张三,李四,孙一 139 男 北京 阿里 王五 139 女 北京 */
create table tb(
t1 varchar(30),t2 varchar(30),
t3 varchar(30),t4 varchar(30),
t5 varchar(30)
)
insert into tb
select '网易', '张三', '139', '男', '北京' union all
select '网易', '李四', '138', '男', '浙江' union all
select '阿里', '王五', '139', '女', '北京' union all
select '网易', '孙一', '135', '男', '浙江'
go
查询:select t1,t2,t3,t4,t5
from
(
select distinct
t1,
stuff((select ','+b.t2 from tb b where a.t1 = b.t1 for xml path(''))
,1,1,'') as t2,
t3,t4,t5,
ROW_NUMBER() over(partition by t1 order by t3 desc) rownum
from tb a
)t
where rownum =1
/*
t1 t2 t3 t4 t5
阿里 王五 139 女 北京
网易 张三,李四,孙一 139 男 北京
*/
create table #tb(web nvarchar(10),cname nvarchar(10),telno nvarchar(20),sex nvarchar(1),area nvarchar(10))
insert into #tb
select '网易','张三',139,'男','北京'
union all select '网易','李四',138,'男','浙江'
union all select '阿里','王五',139,'女','北京'
union all select '网易','孙一',135,'男','浙江'
goselect a.web,stuff((select ','+cname from #tb b
where b.web=a.web --and b.cname=a.cname
for xml path('')),1,1,'') 'cname'
,telno=(select top 1 telno from #tb c where a.web=c.web)
,sex=(select top 1 sex from #tb c where a.web=c.web)
,area=(select top 1 area from #tb c where a.web=c.web)
from #tb a
group by a.web
drop table #tb/*
web cname telno sex area
---------------------------------------------------
网易 张三,李四,孙一 139 男 北京
阿里 王五 139 女 北京
*/
建表,外加建一个函数:
create table tb(
t1 varchar(30),t2 varchar(30),
t3 varchar(30),t4 varchar(30),
t5 varchar(30)
)
insert into tb
select '网易', '张三', '139', '男', '北京' union all
select '网易', '李四', '138', '男', '浙江' union all
select '阿里', '王五', '139', '女', '北京' union all
select '网易', '孙一', '135', '男', '浙江'
goif exists(select * from sys.objects where name = 'fn_mergeSTR')
drop function dbo.fn_mergeSTR
go
create function dbo.fn_mergeSTR(@id varchar(30),
@split varchar(10)) --分隔符
returns varchar(300)
as
begin
declare @str varchar(300);
set @str = '';
--这里的code改为需要合并的字段
select @str = @str + @split + t2
from tb
where t1 = @id
set @str = stuff(@str,1,1,'' )
return @str --返回值
end
go
查询:if OBJECT_ID('tempdb..#temp') is not null
drop table #temp
goselect identity(int,1,1) id,t1,dbo.fn_mergeSTR(t1,',') t2,t3,t4,t5
into #temp
from tb
select t1,t2,t3,t4,t5
from
(
select *,
(select COUNT(*) from #temp b where a.id >= b.id and a.t1 = b.t1) rownum
from #temp a
)t
where rownum = 1
/*
t1 t2 t3 t4 t5
网易 张三,李四,孙一 139 男 北京
阿里 王五 139 女 北京
*/