如何处理从数据库中选择动态属性

I have designed a little database the idea is that I have a table products and each product has its category and dynamic list of properties (those properties are in another table):

我设计了一个小数据库的想法是我有一个表产品,每个产品都有它的类别和动态属性列表(这些属性在另一个表中):

ProductCategories{ProductCategoryId, Name}
Products{ProductId, Name, ProductCategoryId}

I bind product for some category here. Now, each ProductCategory is related to another table where I can add properties for that category:

我在这里绑定某个类别的产品。现在,每个ProductCategory都与另一个表相关,我可以在其中添加该类别的属性:

CategoryProperties{CategoryPropertyId, ProductCategoryId, Name}

Values for each property is in this table:

每个属性的值都在此表中:

ProductPropertyValues{ProductId, CategoryPropertyId, Value}

The problem is when I execute query:

问题是当我执行查询时:

select p.Name as ProductName, m.Name as Manufacturer, pc.Name as Category
from products p
left join dbo.Manufacturers m
on p.ManufactureId = m.ManufactureId
left join dbo.ProductCategories pc
on p.ProductCategoryId = pc.ProductCategoryId

This gives me result like:

这给我的结果如下:

| some product name | RedBull | Can |

But I want to get and all related properties for this product category and that is the problem. When I try this query:

但是我希望获得此产品类别的所有相关属性,这就是问题所在。当我尝试这个查询时:

select p.Name as ProductName, m.Name as Manufacturer, pc.Name as Category, cp.Name,  ppv.Value
from products p
left join dbo.Manufacturers m
on p.ManufactureId = m.ManufactureId
left join dbo.ProductCategories pc
on p.ProductCategoryId = pc.ProductCategoryId

left join dbo.CategoryProperties cp
on pc.ProductCategoryId = cp.ProductCategoryId

left join dbo.ProductPropertyValues ppv
on p.ProductId = ppv.ProductId

After this query instead of one row in result I get '121' results. I don't know what am I doing wrong. I want to get result like:

在此查询之后而不是结果中的一行,我得到'121'结果。我不知道我做错了什么。我想得到如下结果:

| Product name      | Manufacturer | Category | prop1 | prop2     | prop3| Prop4          |      
| some product name | RedBull      | Can      | 34    | something | 45.6 | something else |

What am I doing wrong or this is impossible?
I get some cross join here as I understand it.

我做错了什么或者这是不可能的?据我所知,我在这里得到一些交叉联接。

2 个解决方案

#1

It sounds like you are attempting to PIVOT the data. This will take you row values and convert it into columns.

这听起来像是在尝试PIVOT数据。这将获取行值并将其转换为列。

If you have a set number of properties to convert, then you can hard-code the query into something similar to this:

如果您有一定数量的要转换的属性,那么您可以将查询硬编码为类似于此的内容:

select *
from
(
  select p.Name as ProductName, 
    m.Name as Manufacturer, 
    pc.Name as Category, 
    cp.Name,
    ppv.Value
  from products p
  left join dbo.Manufacturers m
    on p.ManufactureId = m.ManufactureId
  left join dbo.ProductCategories pc
    on p.ProductCategoryId = pc.ProductCategoryId
  left join dbo.CategoryProperties cp
    on pc.ProductCategoryId = cp.ProductCategoryId
  left join dbo.ProductPropertyValues ppv
    on p.ProductId = ppv.ProductId
    and cp.CategoryPropertyId = ppv.CategoryPropertyId
) src
pivot
(
  max(value)
  for Name in ([Code], [Data], etc)
) piv

It gets a little more difficult, if you have an unknown number of values. You will have to use dynamic sql to perform this. I showed you a static version because it is easier to move from a hard-coded version to the dynamic solution.

如果你有一个未知数量的值,它会变得有点困难。您将不得不使用动态SQL来执行此操作。我向您展示了一个静态版本,因为它更容易从硬编码版本转移到动态解决方案。

The key to the dynamic version is getting the column names. The full script will be similar to this:

动态版本的关键是获取列名称。完整的脚本将类似于:

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

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name) 
                    from dbo.CategoryProperties
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ProductName, Manufacturer, Category,' + @cols + ' from 
             (
                select p.Name as ProductName, 
                  m.Name as Manufacturer, 
                  pc.Name as Category, 
                  cp.Name,  
                  ppv.Value
                from products p
                left join dbo.Manufacturers m
                  on p.ManufactureId = m.ManufactureId
                left join dbo.ProductCategories pc
                  on p.ProductCategoryId = pc.ProductCategoryId
                left join dbo.CategoryProperties cp
                  on pc.ProductCategoryId = cp.ProductCategoryId
                left join dbo.ProductPropertyValues ppv
                  on p.ProductId = ppv.ProductId
                  and cp.CategoryPropertyId = ppv.CategoryPropertyId
            ) x
            pivot 
            (
                max(value)
                for Name in (' + @cols + ')
            ) p '

execute(@query)

#2

To answer your question about the cross-join, I think the problem is that you are not tying ProductPropertyValues back to CategoryProperties. Therefore you get all the properties from ProductPropertyValues for all categories for each CategoryProperties row.

要回答有关交叉连接的问题,我认为问题在于您没有将ProductPropertyValues绑定回CategoryProperties。因此,您可以从ProductPropertyValues获取每个CategoryProperties行的所有类别的所有属性。

Now, as far as getting the format you want ... that's called "pivoted", because you want to take a vertical list (all the properties) and "pivot" it, rendering it horizontally. That is tough to do dynamically, because, while SQL Server does have a PIVOT command, it requires that you know all the values that you mean to use for column names at coding time. While that would work if you knew your properties were called "Prop 1", "Prop 2", etc., I assume the names vary with the product.

现在,只要获得你想要的格式......那就叫做“旋转”,因为你想要一个垂直列表(所有属性)并“转动”它,水平渲染它。这很难动态完成,因为虽然SQL Server确实有PIVOT命令,但它要求您知道在编码时用于列名的所有值。如果您知道您的属性被称为“Prop 1”,“Prop 2”等,那么这将起作用,我假设名称因产品而异。

How determined are you to get all the properties in one row? It can be done, but it's going to be complicated.

你如何确定一行中的所有属性?它可以做到,但它会变得复杂。