例:
id txt
1 aaa
1 bbb
2 ccc
3 ddd
3 eee
3 fff
select id,***(txt,';') from tb group by id
结果:
1 aaa;bbb
2 ccc
3 ddd;eee;fff
id txt
1 aaa
1 bbb
2 ccc
3 ddd
3 eee
3 fff
select id,***(txt,';') from tb group by id
结果:
1 aaa;bbb
2 ccc
3 ddd;eee;fff
解决方案 »
- 如何将一个查询出来的结果集直接生成一个新表,十万火急!!
- Unhandled exception in 11.exe(KERNEL 32.DLL): 0xE06D7363: Microsoft C++ Exceptio
- 请问10%该怎样表示呢?
- 大数据查询的问题!请帮忙。
- 难搞的Insert INTO,求大虾帮忙
- SQL 2000
- SQL2005通过Select...Into...方式产生表,列不能为空?
- collation的问题
- 请总高手们,一个关于系统时间的问题?
- 超难,非SQL高手请绕道,如何防止服务器时间调整影响到客户端软件时长计算?
- EXEC sp_dboption 'msdb', 'single user', 'true' 出错
- 有问题~~~
insert test
select 1,'aaa' union all
select 1,'bbb' union all
select 2,'ccc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'fff'
--select * from test
gocreate function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +';' +txt from test where id=@id
--return @s
return stuff(@s,1,1,'')
end
goselect id,dbo.Gettxt(id) txt from test group by id
godrop function Gettxt
drop table test
create table tb(id int,txt varchar(100))
go
insert into tb
select 1,'aaa' union all
select 1,'bbb' union all
select 2,'ccc' union all
select 3,'ddd' union all
select 3,'eee' union all
select 3,'fff'go
--写一个聚合函数:
create function dbo.fn_Merge(@id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+';'+txt from tb where id=@id
return stuff(@r,1,1,'')
end
go-- 调用函数
select id, dbo.fn_Merge(id) as txt from tb group by id
go
drop table tb
drop function fn_Merge
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+@dot+txt from tb where id=@id
set @s=stuff(@s,1,len(@dot),'')
return @s
endselect id,dbo.f_join(id,';') from tb group by id随手乱打的,没测试
--2005系统提供了功能聚合
create table test(id int,iid int,name varchar(40))insert into test values(1,1,'aaa')
insert into test values(2,2,'bbb')
insert into test values(2,2,'ccc')
insert into test values(3,3,'ddd')
insert into test values(4,4,'fff')
GO-- sql 2005
SELECT *
FROM(
SELECT DISTINCT
id, iid
FROM test
)A
OUTER APPLY(
SELECT name = STUFF(REPLACE(REPLACE(
(
SELECT v = name
FROM test t
WHERE id = A.id
AND iid = A.id
FOR XML AUTO
), N'<t v="', N'/'), N'"/>', N''), 1, 1, N'')
)B
GODROP TABLE test-- 结果
id iid name
----------- ----------- ------------
1 1 aaa
2 2 bbb/ccc
3 3 ddd
4 4 fff(4 行受影响)