Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

I have two tables as decribe bellow:

Table1

id | table2_id | value
1  | 1         | AAA
2  | 1         | BBB
3  | 1         | CCC

Table2

id | value
1  | XXXX

and I want to update Table2 with combined value from Table1.

OUTPUT Expected on Table2

id | value
1  | AAA,BBB,CCC

How can I do or there are the best way to get expected result that explained above just using TSQL in sql server?

share|improve this question
you could iterate a cursor to get the combined value, then fire update query on table2. – Sridhar May 2 at 8:10
1  
Why do you want to do that? Storing comma delimited values is not really a good design. – a_horse_with_no_name May 2 at 8:44
Maybe that out of topic wit can be discussed on another thread – Habibillah May 2 at 10:38
add comment (requires an account with 50 reputation)

2 Answers

up vote 3 down vote accepted

you can use Common Table Expression on this,

WITH record
AS
(
    SELECT [table2_id],
            STUFF((SELECT ',' + [value]
                    FROM Table1
                    WHERE [table2_id] = a.[table2_id]
                    FOR XML PATH ('')), 1, 1, '')  AS val_list
    FROM    Table1 AS a
    GROUP   BY [table2_id]
)
UPDATE  a
SET     a.value = b.val_list
FROM    table2 a
        INNER JOIN record b
            ON a.ID = b.table2_id
share|improve this answer
Thank You, You save my time :) – Habibillah May 2 at 8:26
add comment (requires an account with 50 reputation)
up vote 0 down vote

After exploring JW 웃 answer, I have modify and do simplest way as query bellow:

UPDATE table2
SET table2.value = STUFF((SELECT ',' + [value]
                        FROM Table1
                        WHERE [table2_id] = a.[table2_id]
                        FOR XML PATH ('')), 1, 1, '')
FROM Table1 a 
WHERE table2.ID = a.table2_id

Here at SQLFiddle Demo

share|improve this answer
add comment (requires an account with 50 reputation)

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.