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_UserValue
But what I like to have is something like this
Products_Id Products_Name PropA PropB Prop#
1 Prod1 Prod1PropA_UserValue Prod1PropB_UserValue ...
2 Prod2 Prod2PropA_UserValue Prod2PropB_UserValue ....
Finally the Question
I know how to get my desired result by the use of dynamically created SQL's.
But my question is: Can I get the desired result only with "pure" SQL using CTE, PIVOT, etc. excluding stored procedures?
Thank you very much in advance ;)
What I have tried:
Reading about CTE, Pivot and googled a lot. But at the moment I have still no idea :(