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

 
Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s