extract all the rows from one field then store in one row based on the name

Question

I want to store everything in column Lesion into one row. Is it possible to do that? How can I do that?

any advice would help!

Answer 1

try this

DECLARE @source TABLE ( name VARCHAR(100), lesion VARCHAR(20));

INSERT INTO @source(name , lesion)
VALUES ('Bob Desk', '123-456-7899'),
('Bob Desk', '123-456-7899'),
('Don Mouse', '123-456-5555'),
('Mike Keyboard', '123-456-7899'),
('Billy Power', '122-222-1134')



SELECT  DISTINCT name,STUFF(
             (SELECT ',' + lesion
              FROM @source s
              WHERE s.name = s2.name
              FOR XML PATH ('')) 
             , 1, 1, '')

             FROM @source s2