Ryan Gomes Ryan Gomes - 2 months ago 10
SQL Question

Rows to single cell

I would like to get the desired output marked in green

enter image description here

the data points for each id get put into a single cell

Basically take all the events that have happened with A and attach it in the same order

Answer

Use Stuff Function:

DECLARE @tblTest AS Table(
    ID INT,
    EVENT VARCHAR(5)
)

INSERT INTO @tblTest VALUES
(1,'A'),
(1,'A'),
(1,'C'),
(2,'A'),
(2,'B'),
(2,'C')

SELECT DISTINCT
    T1.ID,
    STUFF
    (
         (SELECT '' + convert(varchar(10), T2.EVENT, 120)
          FROM @tblTest T2
          where T1.ID = T2.ID
          FOR XML PATH (''))
    , 1, 0, '')  AS EVENT
FROM @tblTest T1
Comments