For XML PATH to Concatenate Rows

CREATE  TABLE  test (id INT, Code varchar(50), Category  varchar(50), value decimal(6,0))

INSERT INTO test VALUES (1,'AB','XYZ',1000),(2,'BC','PQR',2000),(3,'AB','XYZ',1000),(4,'BC','PQR',2000)

SELECT t1.Code,t1.Category,
       Stuff(( SELECT ',' + Cast( as varchar(5))
           FROM test t2
          WHERE t2.Code = t1.Code and  t2.Category = t1.Category
          ORDER BY id
            FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,'')  AS ids
  FROM test t1
 GROUP BY t1.Code,t1.Category;

 drop table test

--another sample with cross apply
  ,Date   DATE  NOT NULL
  ,Diet   VARCHAR(100)
INSERT INTO mytable(Id,Date,Diet ) 
VALUES (13,'2018-07-02','1" Pieces cut to size'),
  (13,'2018-07-02','1/2" Pieces cut to size'),
 (13,'2018-07-12',null ),
 (13,'2018-07-11',null );
 Select distinct  Id,Date, STUFF(t.Diets,1,1,'')  Diets
  from mytable  m
 cross apply
 (Select Stuff((select ','+ RTRIM(t1.Diet)  Diets 
 from mytable t1
 where and m.Date =t1.Date
 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1,''))    t ( Diets)     

 drop table mytable


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s