CODEWITHSUNDEEP
sql-server-2008
Abhishek Raj
Abhishek Raj

Reputation: 51

how to update a column with multiple row separated by comma in sql server

The are Two table 

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

2nd Table Comment

comment

update the comment column with rows of stud in the given format Expected Result

Table Comment

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

Upvotes: 4

Views: 499

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

SQL Fiddle

MS SQL Server 2008 Schema Setup:

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)

Query 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:

Query 2:

select *
from comment

Results:

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

Upvotes: 4

Related Questions