Updating table based on 2 tables with RowID is as commonality

Question

Hi there

I have a table like this:

RowID; ListDescription1; ListNormalisedDescription1;
1      XXXX       YYYY   NULL
2      ZZZZZ             NULL

I made a complex transformation/normalisation (removing spaces, replacing space and split) and manage to make the same data turning into:

RowID; NormalisedItemDescrption1;
1      XXXX
1      YYYY
2      ZZZZZ

AS you can see the commonality between these 2 tables is RowID.

I want to update ListNormalisedDescription1 based on the table so become:

RowID; ListDescription1; ListNormalisedDescription1;
1      XXXX       YYYY   XXXX;YYYY
2      ZZZZZ             ZZZZZ

Please note that the delimiter is in ';'

I am trying to avoid cursor if it's possible.

Thanks

Answer 1

Assuming SQL Server 2005+, use:

UPDATE table
   SET ListDescription1 = STUFF(ISNULL(SELECT ' ' + x.NormalisedItemDescrption1
                                         FROM NORMALIZED_TABLE x
                                        WHERE x.rowid = rowid
                                     GROUP BY x.NormalisedItemDescrption1
                                      FOR XML PATH ('')), ''), 1, 2, ''),
       ListNormalisedDescription1 = STUFF(ISNULL(SELECT ';' + x.NormalisedItemDescrption1
                                                   FROM NORMALIZED_TABLE x
                                                  WHERE x.rowid = rowid
                                               GROUP BY x.NormalisedItemDescrption1
                                                FOR XML PATH ('')), ''), 1, 2, '')