我有一个表如下:Group_ID Favorite Name
1 pingpang xiao li
1 football xiao wang
2 basketball xiao zhang
2 basketball xiao liu我想用个select语句之后变成如下输出:
Group_ID Favorite Name
1 pingpang,football xiao li,xiao wang
2 basketball xiao zhang,xiao liu要怎么样写啊,谢谢!!!!!!!!!!!!!!!!
1 pingpang xiao li
1 football xiao wang
2 basketball xiao zhang
2 basketball xiao liu我想用个select语句之后变成如下输出:
Group_ID Favorite Name
1 pingpang,football xiao li,xiao wang
2 basketball xiao zhang,xiao liu要怎么样写啊,谢谢!!!!!!!!!!!!!!!!
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(a int,b varchar(10),c varchar(40))
go
insert tb SELECT
1 , 'pingpang' , 'xiao li' UNION ALL SELECT
1 , 'football' , 'xiao wang' UNION ALL SELECT
2 , 'basketball' , 'xiao zhang' UNION ALL SELECT
2 , 'basketball' , 'xiao liu'
go
CREATE FUNCTION dbo.f_tb1(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + b FROM tb WHERE a=@id
RETURN STUFF(@str, 1, 1, '')
END
GO
CREATE FUNCTION dbo.f_tb2(@id int)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @str varchar(8000)
SET @str = ''
SELECT @str = @str + ',' + c FROM tb WHERE a=@id
RETURN STUFF(@str, 1, 1, '')
END
GO SELECT a, b = dbo.f_tb1(a),c= dbo.f_tb2(a)FROM tb GROUP BY a
go
/*------------
a b c
----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 pingpang,football xiao li,xiao wang
2 basketball,basketball xiao zhang,xiao liu(2 行受影响)
-------*/
标题：按某字段合并字符串之一(简单合并)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-11-06
地点：广东深圳描述：将如下形式的数据按id字段合并value字段。
id value
----- ------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
需要得到结果：
id value
------ -----------
1 aa,bb
2 aaa,bbb,ccc
即：group by id, 求 value 的和（字符串相加）
*/
--1、sql2000中只能用自定义的函数解决
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
gocreate function dbo.f_str(@id int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + ',' + cast(value as varchar) from tb where id = @id
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select id , value = dbo.f_str(id) from tb group by iddrop function dbo.f_str
drop table tb------------------------------------------------
--调用函数
select id , value = dbo.f_str(id) from tb group by id--2、sql2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
goselect id, [value] = stuff((select ',' + [value] from tb t where id = tb.id for xml path('')) , 1 , 1 , '')
from tb
group by iddrop table tb
--3、使用游标合并数据
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
declare @t table(id int,value varchar(100))--定义结果集表变量
--定义游标并进行合并处理
declare my_cursor cursor local for
select id , value from tb
declare @id_old int , @id int , @value varchar(10) , @s varchar(100)
open my_cursor
fetch my_cursor into @id , @value
select @id_old = @id , @s=''
while @@FETCH_STATUS = 0
begin
if @id = @id_old
select @s = @s + ',' + cast(@value as varchar)
else
begin
insert @t values(@id_old , stuff(@s,1,1,''))
select @s = ',' + cast(@value as varchar) , @id_old = @id
end
fetch my_cursor into @id , @value
END
insert @t values(@id_old , stuff(@s,1,1,''))
close my_cursor
deallocate my_cursorselect * from @t
drop table tb---------------------------------------------------
create function GetFavourites(@groupid int)
returns varchar(max)
as
declare curF cursor for select distinct Favorite from tb where Group_ID=@groupid
declare @fName varchar(50),@result varchar(max)
fetch next from curF into @fName select @result=@fName
while @@fetch_status=0
begin
select @result=@result+','+@fName
fetch next from curF into @fName
endclose curF
deallocate curF
return @result
类似创建函数GetNames最后使用语句：select group_id, GetFavourites(group_id) as Favorite, GetNames (group_id) as Name
from tb order by group_id