comma delimited column fix

Question

At my current place of employment someone made a decision to put IDs into a comma delimited column. I'm looking to break these out into a proper many to many relationship. However; it is not in the cards for the application that references this data to change right now. So I need to modify a view to still show the comma delimited column. What is the best way to setup the view so that it displays the comma delimited column? Is there a way to do it that doesn't involve leaving the original column?

A stripped down version of the table definitions is as follows.

ID
ProductName
FeatureIDs

ID     ProductName    FeatureIDS
1      Hot dog        1,5,4
2      Hamburger      1,3
3      Fish           2

ID
FeatureName

ID     FeatureName
1      Mayo
2      TarTar Sauce
3      Pickle
4      Relish
5      Onion

Ultimatly I'd like to combine these into a single products table and a many to many relationship

ID ProductName ProductType

where product type would be either 'product' or 'feature'

ID productid featureid

for the mapping table.

Yes the view that is setup needs to be an updateable view.

Answer 1

The view could do something like this (obviously I've guessed at table/column names):

CREATE VIEW dbo.whatever
AS
  SELECT src.columns /*...*/ , IDList = STUFF((SELECT ',' + CONVERT(VARCHAR(12), p.ID)
    FROM dbo.ProperRelationalTable AS p
    WHERE p.EntityID = src.EntityID
    FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 1, '')
  FROM dbo.SourceTable AS src;

However as @Martin pointed out in a comment, this view will break the application if it expects to perform any DML against the view rather than the base tables. You can avert this problem by setting up INSTEAD OF triggers as he explained.

A related post that might be useful in other ways aside from just the presentation aspect:

http://www.mssqltips.com/sqlservertip/2074/make-your-sql-server-database-changes-backward-compatible-when-changing-a-relationship/