5

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.

5
  • 1
    So the underlying table has the proper relationship, and you want the view to show the comma delimited format? Can you please post the DDL of the table and maybe some sample data? Feb 5, 2013 at 14:39
  • 4
    Are the queries against this view SELECT only or is it the target of data modification statements? Feb 5, 2013 at 14:48
  • @Thomas Stringer - I've updated the question with the information
    – Lumpy
    Feb 5, 2013 at 15:22
  • @Martin Smith - Excellent question. The queries include data modification statements.
    – Lumpy
    Feb 5, 2013 at 15:22
  • 1
    @Lumpy - As well as using XML PATH as per @Aaron's answer you'll also need INSTEAD OF triggers on INSERT and UPDATE that split the incoming comma separated values apart then and stores them in the normalised structure (and on DELETE too to remove all the associated rows). Feb 5, 2013 at 15:24

1 Answer 1

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/

1
  • Thanks. Yes the UI is expecting to do DML against the view. I'll read the link
    – Lumpy
    Feb 5, 2013 at 15:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.