how to convert table row to split by comma by duplicate value

Question

I have a table Like below

I want to separate the column by comma if the "StartDate" Field is same like below

EmpID OtherReportID Status StartDate
371    2,381,2       0     2013-11-05 17:59:00:000
371     381          0     2013-11-08 17:59:00:000
371      2           0     2013-11-10 17:59:00:000
371     381          0     2014-08-15 00:00:00:000

I tried the below code, just put the number if it is same

select DENSE_RANK() over(order by startdate) dd,* from Emp_tb_Eob_OtherReportingManager;

Answer 1

What you need to do is the equivalent of a group_concat() in MySQL.

Please look at this: How to make a query with group_concat in sql server

Answer 2

GROUP_CONCATE(), this is what you have to look at.

Try this;

 SELECT EmpID , GROUP_CONCAT(OtherReportID SEPARATOR ', ')
 FROM Emp_tb_Eob_OtherReportingManager GROUP BY DATE(StartDate);

Note: limitation of 1024 bytes in the resulting column.

Update for SQL: Check this it's way to do same thing in SQL. Hope it helps..!!!!

Answer 3

This query will give the expected result.

It will also work in SQL SERVER 2008

SELECT
    T1.EmpID, T1.Status, T1.StartDate , 
    OtherReportId  = 
        stuff((
            SELECT
                ', ' + T2.OtherReportId
            FROM Employee T2
            WHERE
                T2.StartDate = T1.StartDate
            GROUP BY T2.OtherReportId
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'
        ), 1, 2, '')
FROM Employee T1 GROUP BY T1.StartDate