SQL将兼容性矩阵转换为映射表(列转换为行)

I have the following table:

我有以下表格:

I want to convert the matrix to this format:

我想把矩阵转换成这种格式:

where the new mapping table represents the compatibility between a set of options and a set of models that use those options, and the numeric values represent the price of an option for that specific model.

新的映射表表示一组选项和一组使用这些选项的模型之间的兼容性，数值表示该特定模型的选项的价格。

Bare in mind that this is just a small sample from a much bigger table, so the query needs to be more or less dynamic and not hardcoded based on the number of options or models provided in this example.

注意，这只是较大表中的一个小示例，因此查询需要多多少少是动态的，而不是基于本例中提供的选项或模型的数量进行硬编码。

Does anyone know how I can achieve this?

有人知道我是怎么做到的吗?

Best regards,

最好的问候,

2 个解决方案

#1

UnPivot or Gordon's answer would be more performant, but here is an option that will "dynamically" unpivot your data or query without actually using dynamic SQL.

UnPivot或者Gordon的回答可能更高效，但是这里有一个选项可以“动态地”卸载数据或查询，而不实际使用动态SQL。

Example

例子

Select A.OptionID
      ,ModelName  = C.Item
      ,Price      = C.Value
 From  YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') --<< Change to desired datatype
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('OptionID','OtherFieldsToExclude')
             ) C

Returns

#2

I like to do this using outer apply:

我喜欢使用外部应用:

select v.*
from t outer apply
     (values (option_id, 'model1', model1),
             (option_id, 'model2', model2),
             (option_id, 'model3', model3)
     ) v(option_id, model_name, price);

You would just add more rows to the values list for each item you want in the final table.

您只需为最终表中的每个项向值列表添加更多的行。

You can also do this using union all, cross join/case, and unpivot.

您还可以使用union all、cross join/case和unpivot来实现这一点。

However, this method uses lateral joins, and these are very powerful for other purposes as well. This is worthwhile syntax to master.

然而，这种方法使用横向连接，对于其他目的来说也非常强大。这是值得掌握的语法。

EDIT:

编辑:

I'm not really sure what "dynamic" means in this context. You table has columns or it doesn't. You can use dynamic SQL to generate the code based on the input parameters or the layout of a single table. It would follow the same logic as above.

我不确定在这种情况下“动态”是什么意思。表有列或没有列。您可以使用动态SQL根据输入参数或单个表的布局生成代码。它会遵循和上面一样的逻辑。

#1