Click here to Skip to main content
15,867,488 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 ...
SQL
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
SQL
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 ...
SQL
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):

SQL
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
SQL
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 :(
Posted
Updated 27-Nov-19 9:35am
v6
Comments
Afzaal Ahmad Zeeshan 22-Nov-19 17:23pm    
I don't know how you are comparing pivot to a stored procedure? A stored procedure is just a function, where the pivot is something that you are looking for here.

I guess a pivot around PropertyName is something that you want... Because there is no way to return the columns based on properties like ... [PropertyValue] as 'PropA' since the data is intertwined.

Read more on PIVOT here and see if that sample tells you something.
[no name] 22-Nov-19 17:58pm    
"I don't know how you are comparing pivot to a stored procedure? " I do not! How do you arrive to this strange statement?

My only question is: Can I do this with "sql only" or is it only possible with dynamically created sql statements?

"Read more on PIVOT here and see if that sample tells you something.":
I read a lot and came to no solution. That's why my question here....
Afzaal Ahmad Zeeshan 22-Nov-19 18:00pm    
How is dynamically created SQL statements not SQL?
[no name] 22-Nov-19 18:18pm    
Seriously?
Of course at the end a dynamically created sql staement is an sql.

As I stated in the question: "I know how to get my desired result by the use of dynamically created SQL's."

But my question is, can I do it with the standard sql without 'external' help.



There's no way to do this without dynamic SQL, since you can't specify a static list of column names for the result.

Something like this should work:
SQL
DECLARE @columns nvarchar(max) = N'';
DECLARE @columns2 nvarchar(max) = N'';

SELECT 
    @columns += N', ' + QUOTENAME(RTRIM(PropertyName)), 
    @columns2 += N', u.' + QUOTENAME(RTRIM(PropertyName))
FROM 
    (SELECT DISTINCT PropertyName FROM ProductUserProperties) As p
ORDER BY 
    PropertyName
;


DECLARE @sql nvarchar(max) = N'WITH ctePivot As
(
    SELECT MasterId' + @columns + N'
    FROM (SELECT MasterId, PropertyName, PropertyValue FROM ProductUserProperties) As u
    PIVOT ( Max(PropertyValue) FOR PropertyName In (' + STUFF(@columns, 1, 2, N'') + N')) As p
)
SELECT
    p.Id, p.Name' + @columns2 + N'
FROM
    Products As p
    LEFT JOIN ctePivot As u
    ON u.MasterId = p.Id
ORDER BY
    p.Id
;';

EXEC sp_executesql @sql;
The generated dynamic query will look something like:
SQL
WITH ctePivot As
(
    SELECT MasterId, [PropA], [PropB]
    FROM (SELECT MasterId, PropertyName, PropertyValue FROM ProductUserProperties) As u
    PIVOT ( Max(PropertyValue) FOR PropertyName In ([PropA], [PropB])) As p
)
SELECT
    p.Id, p.Name, u.[PropA], u.[PropB]
FROM
    Products As p
    LEFT JOIN ctePivot As u
    ON u.MasterId = p.Id
ORDER BY
    p.Id
;
 
Share this answer
 
 
Share this answer
 
Comments
[no name] 22-Nov-19 13:06pm    
No sorry... I don't like to extend the SQL in case another property will be added. Deserves a 1 but because you try to help it ends in a neutral 3

[Edit]
And please delete your useless answer, that the question remains unanswered and will will hopefully answered by some professionals! You did not even read the details, it seems you googled simply for some keywords. I see meanwhile how you hunt rep....
RickZeeland 23-Nov-19 5:05am    
That is not true, I tried to find an example that resembled your query closely and that took some effort. You are right that CASE is an ugly construction, but better an ugly solution than no solution :)
[no name] 23-Nov-19 6:49am    
I was quite rude, sorry for that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900