Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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? –  Thomas Stringer Feb 5 '13 at 14:39
4  
Are the queries against this view SELECT only or is it the target of data modification statements? –  Martin Smith Feb 5 '13 at 14:48
    
@Thomas Stringer - I've updated the question with the information –  Lumpy Feb 5 '13 at 15:22
    
@Martin Smith - Excellent question. The queries include data modification statements. –  Lumpy Feb 5 '13 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). –  Martin Smith Feb 5 '13 at 15:24
add comment

1 Answer

up vote 1 down vote accepted

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/

share|improve this answer
    
Thanks. Yes the UI is expecting to do DML against the view. I'll read the link –  Lumpy Feb 5 '13 at 15:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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