declare @t table
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into @t values('2009/06/01',8,'apple-')
insert into @t values('2009/06/01',8,'orange-')
insert into @t values('2009/06/01',9,'apple-')
insert into @t values('2009/06/01',9,'pear-')
insert into @t values('2009/06/02',10,'apple-')
insert into @t values('2009/06/02',10,'lizhi-')select * from @t
---想要的结果---------
'2009/06/01' 8 'apple-orange-'
'2009/06/01' 9 'apple-pear-'
'2009/06/02' 8 'apple-lizhi-'
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into @t values('2009/06/01',8,'apple-')
insert into @t values('2009/06/01',8,'orange-')
insert into @t values('2009/06/01',9,'apple-')
insert into @t values('2009/06/01',9,'pear-')
insert into @t values('2009/06/02',10,'apple-')
insert into @t values('2009/06/02',10,'lizhi-')select * from @t
---想要的结果---------
'2009/06/01' 8 'apple-orange-'
'2009/06/01' 9 'apple-pear-'
'2009/06/02' 8 'apple-lizhi-'
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 id
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into @t values('2009/06/01',8,'apple-')
insert into @t values('2009/06/01',8,'orange-')
insert into @t values('2009/06/01',9,'apple-')
insert into @t values('2009/06/01',9,'pear-')
insert into @t values('2009/06/02',10,'apple-')
insert into @t values('2009/06/02',10,'lizhi-')
--查询结果
SELECT distinct Message=stuff((select '' + [Message] from @t where MakeDay=t.MakeDay and BranchCD = t.BranchCD for xml path('')),1,1,''),MakeDay,BranchCD
from @t t
order by MakeDay,BranchCD
/* 结果
Message MakeDay BranchCD
pple-orange- 2009-06-01 00:00:00.000 8
pple-pear- 2009-06-01 00:00:00.000 9
pple-lizhi- 2009-06-02 00:00:00.000 10
*/
这个是正确的。declare @t table
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into @t values('2009/06/01',8,'apple-')
insert into @t values('2009/06/01',8,'orange-')
insert into @t values('2009/06/01',9,'apple-')
insert into @t values('2009/06/01',9,'pear-')
insert into @t values('2009/06/02',10,'apple-')
insert into @t values('2009/06/02',10,'lizhi-')
--查询结果
SELECT distinct Message=stuff((select ' ' + [Message] from @t where MakeDay=t.MakeDay and BranchCD = t.BranchCD for xml path('')),1,1,''),MakeDay,BranchCD
from @t t
order by MakeDay,BranchCD
/* 结果
Message MakeDay BranchCD
apple- orange- 2009-06-01 00:00:00.000 8
apple- pear- 2009-06-01 00:00:00.000 9
apple- lizhi- 2009-06-02 00:00:00.000 10
*/
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html?61024
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into @t values('2009/06/01',8,'apple-')
insert into @t values('2009/06/01',8,'orange-')
insert into @t values('2009/06/01',9,'apple-')
insert into @t values('2009/06/01',9,'pear-')
insert into @t values('2009/06/02',10,'apple-')
insert into @t values('2009/06/02',10,'lizhi-')select makeDay,Branchcd,stuff((select ''+Message from @t where makeDay=a.makeDay and Branchcd=a.Branchcd for xml path('') ),1,1,'') mes from @t a group by makeDay,BranchcdmakeDay Branchcd mes
----------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2009-06-01 00:00:00.000 8 pple-orange-
2009-06-01 00:00:00.000 9 pple-pear-
2009-06-02 00:00:00.000 10 pple-lizhi-
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into ttt values('2009/06/01',8,'apple-')
insert into ttt values('2009/06/01',8,'orange-')
insert into ttt values('2009/06/01',9,'apple-')
insert into ttt values('2009/06/01',9,'pear-')
insert into ttt values('2009/06/02',10,'apple-')
insert into ttt values('2009/06/02',10,'lizhi-')
--drop function f_str;
create function dbo.f_str(@BranchCD int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + Message
from ttt
where BranchCD = @BranchCD
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
select MakeDay , BranchCD, Message= dbo.f_str(BranchCD)
from ttt
group by MakeDay,BranchCD
-----结果---------
--2009-06-01 00:00:00.000 8 pple-orange-
--2009-06-01 00:00:00.000 9 pple-pear-
--2009-06-02 00:00:00.000 10 pple-lizhi-
create table ttt
(
MakeDay datetime
,BranchCD int
,Message varchar(1000)
)
insert into ttt values('2009/06/01',8,'apple-')
insert into ttt values('2009/06/01',8,'orange-')
insert into ttt values('2009/06/01',9,'apple-')
insert into ttt values('2009/06/01',9,'pear-')
insert into ttt values('2009/06/02',10,'apple-')
insert into ttt values('2009/06/02',10,'lizhi-')
drop function f_str;
create function dbo.f_str(@BranchCD int) returns varchar(100)
as
begin
declare @str varchar(1000)
set @str = ''
select @str = @str + Message
from ttt
where BranchCD = @BranchCD
set @str = right(@str , len(@str) - 1)
return @str
end
go--调用函数
--8楼的朋友稍微有点输出错误。注意MakeDay的输出
select distinct convert (varchar(10), MakeDay,120) as MakeDay, BranchCD, Message= dbo.f_str(BranchCD)
from ttt