Combining Multiple Rows into 1 Row (but twice)

Question

My query looks like this:

SELECT
    req.number
    , task.number
    , usr.vendor

FROM req
LEFT JOIN task on req.sys_id = task.req_item
LEFT JOIN usr on usr.sys_id = task.assigned_to

Results look like this:

I want resulst to look like this:

I've been trying to use STUFF but I'm not getting it. Can anyone help?

Thank you

Answer 1

Great explanation here:

How Stuff and 'For Xml Path' work in SQL Server?

--sample data
CREATE TABLE #t1  (rnum VARCHAR(20) NOT NULL, tnum VARCHAR(20) NOT NULL, vendor VARCHAR (20) NOT NULL)

INSERT INTO #t1
VALUES
    ('R1','T1','A'),
    ('R1','T2','A'),
    ('R1','T3','B'),
    ('R2','T4','A'),
    ('R2','T5','A'),
    ('R3','T6','A'),
    ('R3','T6','B'),
    ('R3','T8','C');

SELECT rnum, 
    tnum = STUFF ((SELECT ',' + tnum FROM #t1  t1 WHERE t2.rnum = t1.rnum FOR XML PATH ('')), 1, 1, ''), 
    vendor = STUFF ((SELECT ',' + vendor FROM #t1 t3 WHERE t2.rnum = t3.rnum FOR XML PATH ('')), 1, 1, '' )
FROM #t1 t2
GROUP BY rnum

DROP TABLE #t1