SQL Qry required to get a comma separated string from a table

Question

In my database there is a table with values below

image attached

In need a qry to get the result like this

Answer 1

DECLARE @TAB TABLE(SIDS INT,VALUE VARCHAR(10))

INSERT INTO @TAB

SELECT 1,'ASC'
UNION ALL
SELECT 1,'ASC'
UNION ALL
SELECT 1,'ASC'
UNION ALL
SELECT 2,'SDF'
UNION ALL
SELECT 2,'SFD'
UNION ALL
SELECT 3,'ERF'
UNION ALL
SELECT 3,'ERF1'


SELECT T1.SIDS,VALUE = STUFF((SELECT ','+T2.VALUE FROM @TAB T2 WHERE T1.SIDS = T2.SIDS FOR XML PATH('')),1,1,'')
FROM @TAB T1
GROUP BY T1.SIDS

OUTPUT

SIDS    VALUE
1   ASC,ASC,ASC
2   SDF,SFD
3   ERF,ERF1

Answer 2

Use XML query as below;

DECLARE @tblQuestion AS Table
(
    SID INT,
    Value VARCHAR(50)
)

INSERT INTO @tblQuestion VALUES(1,'stu')
INSERT INTO @tblQuestion VALUES(1,'vtu')
INSERT INTO @tblQuestion VALUES(1,'ztu')
INSERT INTO @tblQuestion VALUES(2,'stu')
INSERT INTO @tblQuestion VALUES(2,'vtu')

select distinct t.SID,
  STUFF((SELECT distinct ', ' + t1.Value
         from @tblQuestion t1
         where t.SID = t1.SID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') Value
from @tblQuestion t;

Output: