How to update column with multiple rows separated by comma in sql server

Question

These are two tables.

table stud id name marks1 marks 2 1 X 3 2 2 y 4 2 3 z 5 2 

Comment of the second table

 comment 

update the comment column with column rows in this format Expected Result

Table Comment

  comment 1,X,3,2#2,y,4,2#3,z,5,2 

Answer 1

SQL Fiddle

Setting up the MS SQL Server 2008 schema :

 create table stud ( id int, name varchar(10), marks1 int, marks2 int ) create table comment ( comment varchar(max) ) insert into stud values (1, 'X', 3, 2), (2, 'y', 4, 2), (3, 'z', 5, 2) 

Request 1 :

 insert into comment(comment) select ( select '#'+cast(id as varchar(10))+','+ name+','+ cast(marks1 as varchar(10))+','+ cast(marks2 as varchar(10)) from stud for xml path(''), type ).value('substring((./text())[1], 2)', 'varchar(max)') 

Results :

Request 2 :

 select * from comment 

Results :

 | COMMENT | --------------------------- | 1,X,3,2#2,y,4,2#3,z,5,2 |