SQL convert compatibility matrix into mapping tabl

问题:

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,

回答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.

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.

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.