問題描述

我有下表:

我想將矩陣轉換成這種格式:

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

退貨

這篇關于SQL將兼容性矩陣轉化為映射表(列轉化為行)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！