Consider the following tables:
Products
ID | Name | etc
1 | Pen | wtv
2 | Pad | wtv
3 | Ball | wtv
...| ... | ...
thousands of products
Categories
ID | Name | etc
1 | Office | wtv
2 | Home | wtv
3 | Park | wtv
...| ... | ...
hundreds of categories
productCategoryMatrix
ProductID | CategoryID
1 | 1
1 | 2
2 | 1
2 | 2
2 | 3
3 | 3
... et cetera
The matrix allows assigning multiple categories to a single product (the typical many-to-many relationship). For example, the Pen belongs to two categories, Office, and Home. I wish to create an editable view of this data which would look like this:
ProductsWithCategories
ProductID | ProductName | Category | Category | Category | Category | Category
1 | Pen | 1 | 2 | | |
2 | Pad | 1 | 2 | 3 | |
3 | Ball | 3 | | | |
| ... Thousands of products ... |
Note that if a product is assigned less than 5 categories, the last columns may remain empty, as in the example. Also note that business logic dictates that there will never be more than 5 categories assigned to a single product, and therefore only five columns are necessary.
Is it possible to create this kind of view with SQL Server 2008 R2? If your solutions also work with SQL Server 2000, that would be awesome as we only upgrade to 2008 in three weeks. If not, no matter, we will wait :)
Any view can be made updatable using an INSTEAD OF trigger.
http://msdn.microsoft.com/en-us/library/ms187956.aspx
In your case, there is no way for SQL Server to automatically make it updatable, but you can map columns to rows in your trigger.
Have a look at these pages:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=120679
SQL pivoted table is read-only and cells can't be edited?
A view can be made from this:
DECLARE @Products TABLE (ID INT, Name VARCHAR(50));
DECLARE @Categories AS TABLE (ID INT, Name VARCHAR(50));
DECLARE @ProductCategoryMatrix AS TABLE (ProductID INT, CategoryID INT);
INSERT INTO @Products (ID, Name) VALUES (1, 'Pen'), (2, 'Pad'), (3, 'Ball');
INSERT INTO @Categories VALUES (1, 'Office'), (2, 'Home'), (3, 'Work');
INSERT INTO @ProductCategoryMatrix VALUES (1, 1), (1, 2), (2, 1), (2, 2), (2, 3), (3, 3);
-- CREATE VIEW AS -- (change variables to real tables)
WITH CategoriesOrdered AS (
SELECT p.ID AS ProductID
,'Cat' + CAST(ROW_NUMBER() OVER(PARTITION BY pc.ProductID ORDER BY pc.CategoryID) AS VARCHAR(1)) AS CategoryColumn
,pc.CategoryID
FROM @Products p
INNER JOIN @ProductCategoryMatrix pc
ON pc.ProductID = p.ID
)
,CategoriesFlat AS (
SELECT *
FROM CategoriesOrdered
PIVOT (MIN(CategoryID) FOR CategoryColumn IN ([Cat1], [Cat2], [Cat3], [Cat4], [Cat5])) AS pvt
)
,ProductsWithCategories AS (
SELECT *
FROM @Products p
LEFT JOIN CategoriesFlat cf
ON cf.ProductID = p.ID
)
SELECT *
FROM ProductsWithCategories;
Making a trigger on this would involve quite a bit of code, but not terribly difficult.