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
peterslove53Asked:
Who is Participating?
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:
DECLARE @TableA TABLE (
    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')

DECLARE @TableB TABLE (
    ID      tinyint      NOT NULL,
    Descr   varchar(max) NOT NULL )

INSERT  INTO @TableB
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

SELECT * FROM @TableB

Open in new window

0

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.
0
peterslove53Author Commented:
@ dsacker Looks cool, what does the  '3, 999999' part in the  ' FOR XML PATH ('')), 3, 999999) Descr'
mean?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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?
0
dsackerContract ERP Admin/ConsultantCommented:
Yes. Wrap the entire SELECT statement in parentheses and treat it as a sub select.
0
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
0
dsackerContract ERP Admin/ConsultantCommented:
The update statement will look like this:
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.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.
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.