create table tt
(
msgid int,
logdate datetime,
msg varchar(20)
)
insert into tt
select 1,'2010-05-05 08:30:00.000','hello' union all
select 1,'2010-05-05 09:05:08.000','aaaa' union all
select 1,'2010-05-05 09:30:00.000','bbbb' union all
select 1,'2010-05-05 10:30:00.000','over' union all
select 2,'2010-05-05 10:08:00.000','send' union all
select 2,'2010-05-05 10:10:00.000','accept' union all
select 3,'2010-05-05 11:10:00.000','end'结果
--------------------------------------------------------------------------------------------------------------
1 hello 2010-05-05 08:30:00.000 aaaa 2010-05-05 09:05:08.000 bbbb 2010-05-05 09:30:00.000 over 2010-05-05 10:30:00.000
2 send 2010-05-05 10:08:00.000 accept 2010-05-05 10:10:00.000
3 end 2010-05-05 11:10:00.000
(select ' '+convert(varchar(20),logdate,120)+msg
from tt
where msgid=t.msgid for xml path(''))结果
from tt t
group by msgid
(
msgid int,
logdate datetime,
msg varchar(20)
)
insert into tt
select 1,'2010-05-05 08:30:00.000','hello' union all
select 1,'2010-05-05 09:05:08.000','aaaa' union all
select 1,'2010-05-05 09:30:00.000','bbbb' union all
select 1,'2010-05-05 10:30:00.000','over' union all
select 2,'2010-05-05 10:08:00.000','send' union all
select 2,'2010-05-05 10:10:00.000','accept' union all
select 3,'2010-05-05 11:10:00.000','end'select ltrim(msgid) +
(select ' '+convert(varchar(20),logdate,120)+' '+msg
from tt
where msgid=t.msgid for xml path(''))结果
from tt t
group by msgid
go
drop table tt
/*
结果
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 2010-05-05 08:30:00 hello 2010-05-05 09:05:08 aaaa 2010-05-05 09:30:00 bbbb 2010-05-05 10:30:00 over
2 2010-05-05 10:08:00 send 2010-05-05 10:10:00 accept
3 2010-05-05 11:10:00 end(3 個資料列受到影響)*/
--2005
select msgid, [value] = stuff((select ' ' + msg+' '+convert(varchar(30),logdate,121) from tt t where msgid = tt.msgid for xml path('')) , 1 , 1 , '')
from tt
group by msgidmsgid value
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 hello 2010-05-05 08:30:00.000 aaaa 2010-05-05 09:05:08.000 bbbb 2010-05-05 09:30:00.000 over 2010-05-05 10:30:00.000
2 send 2010-05-05 10:08:00.000 accept 2010-05-05 10:10:00.000
3 end 2010-05-05 11:10:00.000(3 行受影响)
DROP TABLE [tt]
GO
create table tt
(
msgid int,
logdate datetime,
msg varchar(20)
)
insert into tt
select 1,'2010-05-05 08:30:00.000','hello' union all
select 1,'2010-05-05 09:05:08.000','aaaa' union all
select 1,'2010-05-05 09:30:00.000','bbbb' union all
select 1,'2010-05-05 10:30:00.000','over' union all
select 2,'2010-05-05 10:08:00.000','send' union all
select 2,'2010-05-05 10:10:00.000','accept' union all
select 3,'2010-05-05 11:10:00.000','end'select distinct 结果=LTRIM((select ' '+ltrim(msgid)+CONVERT(varchar,logdate,120)+' '+msg from tt where msgid=t.msgid for xml path('')))
from tt t
/*
结果
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
12010-05-05 08:30:00 hello 12010-05-05 09:05:08 aaaa 12010-05-05 09:30:00 bbbb 12010-05-05 10:30:00 over
22010-05-05 10:08:00 send 22010-05-05 10:10:00 accept
32010-05-05 11:10:00 end(3 行受影响)
*/
DROP TABLE [tt]
GO
create table tt
(
msgid int,
logdate datetime,
msg varchar(20)
)
insert into tt
select 1,'2010-05-05 08:30:00.000','hello' union all
select 1,'2010-05-05 09:05:08.000','aaaa' union all
select 1,'2010-05-05 09:30:00.000','bbbb' union all
select 1,'2010-05-05 10:30:00.000','over' union all
select 2,'2010-05-05 10:08:00.000','send' union all
select 2,'2010-05-05 10:10:00.000','accept' union all
select 3,'2010-05-05 11:10:00.000','end'select distinct 结果=LTRIM((select ' '+ltrim(msgid)+' '+CONVERT(varchar,logdate,120)+' '+msg from tt where msgid=t.msgid for xml path('')))
from tt t
/*
结果
---------------------------------------------------------------------------------------------------------------------
1 2010-05-05 08:30:00 hello 1 2010-05-05 09:05:08 aaaa 1 2010-05-05 09:30:00 bbbb 1 2010-05-05 10:30:00 over
2 2010-05-05 10:08:00 send 2 2010-05-05 10:10:00 accept
3 2010-05-05 11:10:00 end(3 行受影响)
*/modify
--2000
--合并处理函数
CREATE FUNCTION dbo.f_str(@col1 varchar(10))
RETURNS varchar(100)
AS
BEGIN
DECLARE @re varchar(100)
SET @re=''
SELECT @re=@re+' '+msg+' '+convert(varchar(30),logdate,121)
FROM tt
WHERE msgid=@col1
RETURN(STUFF(@re,1,1,''))
END
GO--调用函数
SELECT msgid,value=dbo.f_str(msgid) FROM tt GROUP BY msgid/*
msgid value
----------- ----------------------------------------------------------------------------------------------------
1 hello 2010-05-05 08:30:00.000 aaaa 2010-05-05 09:05:08.000 bbbb 2010-05-05 09:30:00.000 over 2010-0
2 send 2010-05-05 10:08:00.000 accept 2010-05-05 10:10:00.000
3 end 2010-05-05 11:10:00.000(3 行受影响)*/