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)

   @num1 In (select ID from TableA)
  DECLARE @colors VARCHAR(1024)
    @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
set Desc= @colors
where ID in (select top 1 ID from TableA)

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dsackerContract ERP Admin/ConsultantCommented:
Here is a complete solution using @TableA and @TableB, which I created from your data above:
    ID      tinyint     NOT NULL,
    Descr   varchar(8)  NOT NULL    )

INSERT INTO @TableA VALUES (20, 'blue')
INSERT INTO @TableA VALUES (20, 'green')
INSERT INTO @TableA VALUES (20, 'black')
INSERT INTO @TableA VALUES (30, 'blank')
INSERT INTO @TableA VALUES (30, 'vas')
INSERT INTO @TableA VALUES (30, 'teren')
INSERT INTO @TableA VALUES (40, 'books')
INSERT INTO @TableA VALUES (40, 'pens')
INSERT INTO @TableA VALUES (40, 'pencils')

    ID      tinyint      NOT NULL,
    Descr   varchar(max) NOT NULL )

        SUBSTRING((SELECT  ', ' + Descr
                   FROM    @TableA a2
                   WHERE   a2.ID = a1.ID
                   FOR XML PATH ('')), 3, 999999) Descr
FROM    @TableA a1


Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dsackerContract ERP Admin/ConsultantCommented:
The FOR XML PATH ('') is one of the coolest tricks in SQL SERVER ever. You'll enjoy studying up on it.
peterslove53Author Commented:
@ dsacker Looks cool, what does the  '3, 999999' part in the  ' FOR XML PATH ('')), 3, 999999) Descr'
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

peterslove53Author Commented:
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?
dsackerContract ERP Admin/ConsultantCommented:
Yes. Wrap the entire SELECT statement in parentheses and treat it as a sub select.
peterslove53Author Commented:
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
dsackerContract ERP Admin/ConsultantCommented:
The update statement will look like this:
SET b.Descr = a.Descr
FROM @TableB b
        SUBSTRING((SELECT  ', ' + Descr
                   FROM    @TableA a2
                   WHERE   a2.ID = a1.ID
                   FOR XML PATH ('')), 3, 999999) Descr
FROM    @TableA a1
ON a.ID = b.ID

Open in new window

The 3, 999999 is part of the SUBSTRING. Without the "3", you'd have a string starting with a comma and a space (", "). You can run the SELECT statement within the SUBSTRING alone to see what I mean.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.