PIVOT and INNER JOIN on dynamically created key/value pairs

Question

In my database I have a few products. Those products have an unknowningly amount of parameters/fields stored as a name and value in a separate table.

http://sqlfiddle.com/#!18/f3b3e

CREATE TABLE Products
    ([ProductId] varchar(50), [Name] varchar(50))
;
    
INSERT INTO Products
    ([ProductId], [Name])
VALUES
    ('PROD1', 'Product 1'),
    ('PROD2', 'Product 2'),
    ('PROD3', 'Product 3')
;

CREATE TABLE ProductFields
    ([ProductId] varchar(50), [Name] varchar(50), [Value] varchar(50))
;
    
INSERT INTO ProductFields
    ([ProductId], [Name], [Value])
VALUES
    ('PROD1', 'Color', 'Red'),
    ('PROD1', 'Size', '2'),
    ('PROD1', 'Weight', '50'),
    ('PROD2', 'Color', 'Blue'),
    ('PROD2', 'Size', '1'),
    ('PROD2', 'Weight', '15'),
    ('PROD3', 'Color', 'Yellow'),
    ('PROD3', 'Size', '3'),
    ('PROD3', 'Weight', '10')
;

If I have 3 products, I want my output to contain 3 rows that looks like this:

ProductId   Name        Color     Size     Weight
----------- ----------- --------- -------- ---------
PROD1       Product 1   Red       2        50
PROD2       Product 2   Blue      1        15
PROD3       Product 3   Yellow    3        10

How do I create a dynamic PIVOT that also has an INNER JOIN against that other table? All values are nice and simply VARCHARs, so that should be quite easy, however, I can't wrap my head around PIVOTs with dynamic values.

This is my go at it:

DECLARE 
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(pf.Name) 
            FROM ProductFields pf
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @query = 'SELECT p.ProductId, p.Name, ' + @cols + ' from 
            (
                SELECT p.ProductId, p.Name FROM Products p
                INNER JOIN ProductFields pf
                ON pf.ProductId = p.ProductId
           ) x
            pivot 
            (
                Value
                for Name in (' + @cols + ')
            ) pi '


execute(@query)

You *really* need to fix your design; that's the real problem here. — Thom A, Apr 15 '21 at 14:44
Does this answer your question? [(SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Apr 15 '21 at 14:45
@Larnu It does not, because there are no JOINs. I tried, but couldn't wrap my head around implementing a JOIN. — MortenMoulder, Apr 15 '21 at 14:47
Then put the joins in the dynamic SQL. Just because it doesn't have joins in it doesn't mean to doesn't answer it. — Thom A, Apr 15 '21 at 14:47
@Larnu And yes, I am simply making an example. This is not how it is in the real world, but there is definitely a table that contains a key/value pair of parameters :-) — MortenMoulder, Apr 15 '21 at 14:48
*"This is not how it is in the real world, but there is definitely a table that contains a key/value pair of parameters"* And they are all a design flaw in a relational database; they break the fundamental rules of normalisation. EAV tables are notoriously difficult to work with in a relational database, and for good reason. — Thom A, Apr 15 '21 at 14:48
@Larnu Let's not discuss that. I'm using a CMS so there is no possible way I can change the database schema :-) — MortenMoulder, Apr 15 '21 at 14:49
Which is why you need a dynamic pivot, as shown in the suggested duplicate. — Thom A, Apr 15 '21 at 14:50
@Larnu I tried again. Completely from scratch using your example. I cannot get it to work. — MortenMoulder, Apr 15 '21 at 15:06
There is no aggregation in your `PIVOT`. `Value for Name` isn't valid; `Value` isn't being aggregated. — Thom A, Apr 15 '21 at 15:09
@Larnu Maybe I am misunderstanding the use of PIVOT? I don't want a sum, count, or anything like that. I just want the value from the other table as it is.. but I guess that's not how it works..? — MortenMoulder, Apr 15 '21 at 15:12
Pivoting is a form of aggregation, you *must* aggregate. See the linked duplicate and you'll see that they are aggregating in the answer. Don't forget the basics of debugging dynamic SQL; `PRINT`/`SELECT` the dynamic SQL, debug that, propagate the solution. — Thom A, Apr 15 '21 at 15:13
@Larnu Sooooo is it because the same key can appear multiple times? I honestly don't know. — MortenMoulder, Apr 15 '21 at 15:15

score 2 · Accepted Answer · answered Apr 15 '21 at 15:15

Perhaps this will help. Notice the inclusion of ITEM and max(Value)

Example or dbFiddle

DECLARE 
@cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(pf.Name) 
            FROM ProductFields pf
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


SET @query = 'SELECT * 
                from (
                       SELECT p.ProductId
                             ,p.Name
                             ,Item=pf.Name
                             ,pf.Value 
                         FROM Products p
                         JOIN ProductFields pf
                           ON pf.ProductId = p.ProductId
                     ) x
            pivot 
            (
                max(Value)
                for Item in (' + @cols + ')
            ) pi '


execute(@query)

Thanks for the reply - that result from dbFiddle is exactly what I was looking for. Not sure why I am getting `Must declare the scalar variable "@cols"` on SQLFiddle, but what you got seems to work! Thank you :) — MortenMoulder, Apr 15 '21 at 15:18
@MortenMoulder If you have `STRING_AGG` available on your version of SQL Server you should use that instead of `FOR XML` — Charlieface, Apr 15 '21 at 15:20

PIVOT and INNER JOIN on dynamically created key/value pairs

1 Answers1