update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

update

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-05-19 : 10:31:27

How do i update Field3 if there is same value in Field1, add all row value with seperate ;

declare @tbl1 table
(ID INT,
Field1 varchar(50),
Field2 varchar(50),
Field3 varchar (255)
)

INSERT INTO @tbl1
SELECT 1, 1,'Abc',null

INSERT INTO @tbl1
SELECT 2,1,'Bcc',null

INSERT INTO @tbl1
SELECT 3,1,'Mno',null

INSERT INTO @tbl1
SELECT 4,2,'Xyz',null

INSERT INTO @tbl1
SELECT 5,2,'XXX', null

Output like this :
1 1 Abc Abc;Bcc;Mno
2 1 Bcc Abc;Bcc;Mno
3 1 Mno Abc;Bcc;Mno
4 2 Xyz Xyz;XXX
5 2 XXX Xyz;XXX

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-05-19 : 11:42:31

Try this

UPDATE t1

SET Field3 = t2.NewField

FROM
@tbl1 t1
INNER JOIN

(

SELECT DISTINCT s1.id,s1.Field2,s1.Field1,
STUFF((SELECT TOP 100 PERCENT ',' + s2.Field2 FROM @tbl1 AS s2 WHERE s2.Field1 = s1.Field1 ORDER BY ',' + s2.Field1 FOR XML PATH('')), 1, 1, '') AS NewField
FROM @tbl1 AS s1
) t2
ON
t1.id = t2.id

Jim

Everyday I learn something that somebody else already knew