如何对这个SQL Server表进行非规范化处理？
我有一个非常宽的表非规范化的（如果你能说）这样的，选项栏去100+如何对这个SQL Server表进行非规范化处理？
Year ProductID ProductName Option1 Option2 Option3 ....Option100
-----------------------------------------------------------------
2016 1 Test1 A1 A1a A3
2015 1 Test1 A1 A2 A2a
问题是我们有动态查询试图确定选项列，然后找到期权价值在他们
即
@SQL= 'SELECT Option' + @getOptionNum +' FROM ProductMapping
理想我希望这转化为这样的事情
Year ProductID ProductName OptionName
-------------------------------------
2016 1 Test1 Option1
2016 1 Test1 Option2
2016 1 Test1 Option3
2015 1 Test1 Option1
2015 1 Test1 Option2
2015 1 Test1 Option3
OptionID OptionName OptionValue Year
-------------------------------------
1 Option1 A1 2016
2 Option2 A1a 2016
3 Option3 A3 2016
4 Option1 A1 2015
5 Option2 A2 2015
6 Option3 A2a 2015
SELECT *
FROM ProductMapping map
LEFT JOIN OptionList list ON map.OptionName = list.OptionName
AND map.Year = list.Year
AND map.OptionName = 'Option1'
我遇到的问题是如何通过查询将该宽表转换为两个表结构，因为它有很多列和行，并且我无法手动规范化所有这些。
是的，我也明白了理想的第二个表需要进一步保持选项1 ... 2选项在单独的表，并在一个单独的表Option1..A1映射的标准，但它是一个开始......
希望简单的例子揭示了以下事实
- 选项1 ... 100列需要在一个单独的表被归
- 选项列值映射的光每年都在变化
有什么想法？
Declare @YourTable table (Year int,ProductID int,ProductName varchar(50),Option1 varchar(50),Option2 varchar(50),Option3 varchar(50))
Insert into @YourTable values
(2016,1,'Test1','A1','A1a','A3'),
(2015,1,'Test1','A1','A2','A2a')
Declare @XML xml
Set @XML = (Select * from @YourTable for XML RAW)
Select *
From (
Select Year = r.value('@Year','int')
,ProductID = r.value('@ProductID','int')
,ProductName = r.value('@ProductName','varchar(50)')
,OptionName = Attr.value('local-name(.)','varchar(100)')
From @XML.nodes('/row') as A(r)
Cross Apply A.r.nodes('./@*[local-name(.)!="ProductID"]') as B(Attr)
) A
Where OptionName Like 'Option%'
Order by Year Desc,OptionName
Select OptionID=Row_Number() over (Order By Year Desc,OptionName),*
From (
Select OptionName = Attr.value('local-name(.)','varchar(100)')
,OptionValue = Attr.value('.','varchar(100)')
,Year = r.value('@Year','int')
From @XML.nodes('/row') as A(r)
Cross Apply A.r.nodes('./@*[local-name(.)!="ProductID"]') as B(Attr)
--CROSS APPLY A.r.nodes('./@*') AS B(Attr)
) A
Where OptionName Like 'Option%'
返回
Year ProductID ProductName OptionName
2016 1 Test1 Option1
2016 1 Test1 Option2
2016 1 Test1 Option3
2015 1 Test1 Option1
2015 1 Test1 Option2
2015 1 Test1 Option3
和
OptionID OptionName OptionValue Year
1 Option1 A1 2016
2 Option2 A1a 2016
3 Option3 A3 2016
4 Option1 A1 2015
5 Option2 A2 2015
6 Option3 A2a 2015
编辑
现在，如果你想直Normaliz通货膨胀
Declare @YourTable table (Year int,ProductID int,ProductName varchar(50),Option1 varchar(50),Option2 varchar(50),Option3 varchar(50))
Insert into @YourTable values
(2016,1,'Test1','A1','A1a','A3'),
(2015,1,'Test1','A1','A2','A2a')
Declare @XML xml
Set @XML = (Select * from @YourTable for XML RAW)
Select ID = r.value('@id','int') --<<'@id' Should be YOUR PK
,Item = Attr.value('local-name(.)','varchar(100)')
,Value = Attr.value('.','varchar(max)')
From @XML.nodes('/row') as A(r)
Cross Apply A.r.nodes('./@*[local-name(.)!="id"]') as B(Attr) --<<'id' Should be YOUR PK
--CROSS APPLY A.r.nodes('./@*') AS B(Attr)
返回（空值通常是你PK）
ID Item Value
NULL Year 2016
NULL ProductID 1
NULL ProductName Test1
NULL Option1 A1
NULL Option2 A1a
NULL Option3 A3
NULL Year 2015
NULL ProductID 1
NULL ProductName Test1
NULL Option1 A1
NULL Option2 A2
NULL Option3 A2a
这绝对是辉煌！工程...但是一些OptionX列有NULL，年显示为NULL为他们？ – SQLSeeker
@SQLSeeker让我再试一次吧 –
@SQLSeeker刚刚在2017年添加了虚拟行，在Option3中为null。我在2012年看到两行（每组）。也许你可以提供样本 –
我会正常化表到一个表中，每个选项行：
select pm.year, pm.productid, pm.productname, v.option, v.optionvalue
from productmapping pm cross apply
(values ('option1', option1), ('option2', option2), . . .
) v(option, optionvalue);
我挣扎把它放到两张表中。我可以想象给选项的ID（但不是选项/值对）。
您是否考虑过使用文档存储数据库而不是关系型（表）数据库？可扩展列是传统RDBMS的一个弱点。 – Dai
太多的现有流程依赖于这些核心表......我正在尝试将我的替代方案作为概念验证来摆脱我们正在进行的所有动态查询 – SQLSeeker
为什么您需要两个表格解决方案？这是什么让你无法用标准化的单表获得？只需在第一个表中添加'OptionValue'，你就可以将一切表格中的所有东西都归一化。 –