How I can show detail rows as columns (free configurable user properties) with MS SQL
-
Dear Experts Please enlighten me. Please Note: The following is reduced to a minimum to keep it simple. In the original design there is at least one table more to hold the UserPropertyDefinitions and some other stuff to keep it more general to hold UserProperties and their definitions for several tables. Let assume we have a Master table ...
CREATE TABLE Products
(
Id int,
Name char(50)
);
INSERT INTO Products(Id, Name) VALUES (1, 'Prod1'),(2, 'Prod2');... and a table which holds the user defined properties and values
CREATE TABLE ProductUserProperties
(
Id int,
MasterId int,
PropertyName char(50),
PropertyValue char(50)
);
INSERT INTO ProductUserProperties (Id, MasterId, PropertyName, PropertyValue) VALUES
(1, 1, 'PropA', 'Prod1PropA_UserValue'),
(2, 1, 'PropB', 'Prod1PropB_UserValue'),
(3, 2, 'PropA', 'Prod2PropA_UserValue'),
(4, 2, 'PropB', 'Prod2PropB_UserValue');A simple query by left joins ...
SELECT
Products.Id Products_Id,
Products.Name Products_Name,
ProductUserProperties.Id ProductUserProperties_Id,
ProductUserProperties.MasterId ProductUserProperties_MasterId,
ProductUserProperties.PropertyName ProductUserProperties_PropertyName,
ProductUserProperties.PropertyValue ProductUserProperties_PropertyValue
FROM Products
LEFT JOIN ProductUserProperties ON ProductUserProperties.MasterId = Products.Id
ORDER BY Products.Id, ProductUserProperties.PropertyName...results in (column names shortened):
Prod_Id Prod_Name ProdUsrProps_Id ProdUsProp_MstrId PropertyName PropertyValue
1 Prod1 1 1 PropA Prod1PropA_UserValue
1 Prod1 2 1 PropB Prod1PropB_UserValue
2 Prod2 3 2 PropA Prod2PropA_UserValue
2 Prod2 4 2 PropB Prod2PropB_UserValueBut what I like to have is something like this
Products_Id Products_Name PropA PropB ... and more properties
1 Prod1 Prod1PropA_UserValue Prod1PropB_UserValue ...
2 Prod2 Prod2PropA_UserValue Prod2PropB_UserValue ....Finally the Question