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

2021-09-10 00:00:00 sql tsql sql-server

我有下表:

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

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?

最好的问候，

推荐答案

UnPivot 或 Gordon 的答案会更高效，但这里有一个选项可以动态"取消数据或查询，而无需实际使用动态 SQL.

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.

示例

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

退货

相关文章