Combine output of SQL stored proc to single row if multiple rows have been returns using comma delimiter

I am trying to write a stored Procedure which returns values in such a way that if couple of rows have all same values except few columns then the SP should return a single row with columns having distinct values between the two rows be merged in to one delimited by a comma Ex: when a I run the SP it returns 2 rows like below. Both the rows have all columns but one similar.

col1               col2   col3               col4                    col5  col6         col7
------------------ ------ ------------------ ----------------------- ----开发者_开发技巧- ------------ --------------
Remote Observation sdgfdg Remote Observation 2011-07-21 00:00:00.000 14.00 Inbound Call Order
Remote Observation sdgfdg Remote Observation 2011-07-21 00:00:00.000 14.00 Inbound Call Status Inquiry

Now my desired output is

col1               col2   col3               col4                    col5  col6         col7
------------------ ------ ------------------ ----------------------- ----- ------------ ---------------------
Remote Observation sdgfdg Remote Observation 2011-07-21 00:00:00.000 14.00 Inbound Call Order, Status Inquiry

Does someone know how to achieve this.

There a many ways to concat rows into strings. Here is one way :)

You can read more at: http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

DECLARE @TestTable TABLE (
    col1 varchar(50),
    col2 varchar(50),
    col3 varchar(50),
    col4 varchar(50),
    col5 varchar(50),
    col6 varchar(50),
    col7 varchar(50)
)

insert into @TestTable 
values
('Remote Observation','sdgfdg','Remote Observation','2011-07-21 00:00:00.000 ','14.00','Inbound Call','Order'),
('Remote Observation','sdgfdg','Remote Observation','2011-07-21 00:00:00.000 ','14.00','Inbound Call','Status Inquiry'),
('Remote Observation','sdgfdg','Remote Observation','2011-07-21 00:00:00.000 ','14.00','Inbound Call','Status Inquiry')


select col1,col2,col3,col4,col5,col6,LEFT(col7,LEN(col7)-1) col7
from
(
    select col1,col2,col3,col4,col5,col6,
    ( 
        select distinct col7 + ',' 
        from @TestTable t2
        where t2.col1 = t1.col1
        AND t2.col2 = t1.col2
        AND t2.col3 = t1.col3
        AND t2.col4 = t1.col4
        AND t2.col5 = t1.col5
        AND t2.col6 = t1.col6
        for xml path('') 
    ) col7
    from @TestTable t1
    group by col1,col2,col3,col4,col5,col6
)  source