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

解决方案 »

  1.   

    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
      

  2.   

    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 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 個資料列受到影響)*/
      

  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 行受影响)
      

  4.   

    IF OBJECT_ID('[tt]') IS NOT NULL
    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 行受影响)
    */
      

  5.   

    IF OBJECT_ID('[tt]') IS NOT NULL
    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
      

  6.   


    --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 行受影响)*/