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(t2.id 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
CREATE TABLE mytable(
   Id     INTEGER  NOT NULL 
  ,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 m.id= t1.id 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:

WordPress.com Logo

You are commenting using your WordPress.com 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