How to concatenate the duplicate values on the basis of a value

Question

I have a scenario where In my table there are lots of duplicate value. lets's say.

SID|LOTID|TEST
1    45    A
1    45    B
1    45    C

I want a mysql query which can provide me output like this.

SID|LOTID|TEST
1    45    A,B,C

Answer 1

Please try the below code. This is working fine with SQL Server 2012

DECLARE @Table TABLE (SID int, LOTID int, TEST CHAR(2))
INSERT INTO @Table
(SID,LOTID,TEST)
VALUES

(1,45,'A'),
(1,45,'B'),
(1,45,'C') 

SELECT SID,LOTID, TEST = 
    STUFF((SELECT ', ' + TEST
           FROM @Table b 
           WHERE b.SID = a.SID 
          FOR XML PATH('')), 1, 2, '')
FROM @Table a
GROUP BY SID,LOTID