peterslove53
asked on
Update field as string from column Values
Hi guyz,
I have this challenging problem. I have 2 tables : Table A and Table B, the two tables are linked by an ID column.
I want to update the 'Desc' field of table B as a comma delimited string pulled from the description column in Table A
linked by the ID field as given below;
Table A
ID Desc
20 blue
20 green
20 black
30 blank
30 vas
30 teren
40 books
40 pens
40 pencils
Desired updated Table B;
Table B
ID Desc
20 blue,green,black
30 blank,vas,teren
40 books,pens,pencils
How do i achieve this. I have a query that transforms the column into string but the update statement does not work properly"
declare @num1 int
select @num1 = (select top 1 ID from TableA)
while
@num1 In (select ID from TableA)
BEGIN
DECLARE @colors VARCHAR(1024)
SELECT
@colors = COALESCE(@colors + ',', '') + (tbl1.Desc)
FROM TableA tbl1,TableB tbl2
where tbl1.ID = tbl2.ID
and rpt.ID = @num1
SELECT Colors = @colors
---Result: blue,green,black
UPDATE TableB
set Desc= @colors
where ID in (select top 1 ID from TableA)
END
I have this challenging problem. I have 2 tables : Table A and Table B, the two tables are linked by an ID column.
I want to update the 'Desc' field of table B as a comma delimited string pulled from the description column in Table A
linked by the ID field as given below;
Table A
ID Desc
20 blue
20 green
20 black
30 blank
30 vas
30 teren
40 books
40 pens
40 pencils
Desired updated Table B;
Table B
ID Desc
20 blue,green,black
30 blank,vas,teren
40 books,pens,pencils
How do i achieve this. I have a query that transforms the column into string but the update statement does not work properly"
declare @num1 int
select @num1 = (select top 1 ID from TableA)
while
@num1 In (select ID from TableA)
BEGIN
DECLARE @colors VARCHAR(1024)
SELECT
@colors = COALESCE(@colors + ',', '') + (tbl1.Desc)
FROM TableA tbl1,TableB tbl2
where tbl1.ID = tbl2.ID
and rpt.ID = @num1
SELECT Colors = @colors
---Result: blue,green,black
UPDATE TableB
set Desc= @colors
where ID in (select top 1 ID from TableA)
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
dsacker
The FOR XML PATH ('') is one of the coolest tricks in SQL SERVER ever. You'll enjoy studying up on it.
ASKER
@ dsacker Looks cool, what does the '3, 999999' part in the ' FOR XML PATH ('')), 3, 999999) Descr'
mean?
mean?
ASKER
I should have added that tableB is already having the IDs, so it is more like an update or append of the comma delimiters to col 'Desc" in table B. Will this solution apply in that scenario?
Yes. Wrap the entire SELECT statement in parentheses and treat it as a sub select.
ASKER
i see @TableA a1 is referred twice, ( @TableA a1 and @TableA a2), so for an update scenario, with IDs already in tableB, how ll the sql syntax be constructed? Could you throw more light to that and also the meaning of '3, 999999' in the statement? Thanks alot
The update statement will look like this:
The @TableA must be referenced twice, because the FOR XML PATH is for a single field only, which results in the comma-delimited string you're looking for, so it must be run by itself and connected to your ID.
UPDATE b
SET b.Descr = a.Descr
FROM @TableB b
JOIN (SELECT a1.ID,
SUBSTRING((SELECT ', ' + Descr
FROM @TableA a2
WHERE a2.ID = a1.ID
FOR XML PATH ('')), 3, 999999) Descr
FROM @TableA a1
GROUP BY a1.ID) a
ON a.ID = b.IDThe @TableA must be referenced twice, because the FOR XML PATH is for a single field only, which results in the comma-delimited string you're looking for, so it must be run by itself and connected to your ID.