将表转换为键值对

Question

我想让当前的表变平。目前，我能够通过迭代方法实现这一点，但运行时间非常糟糕。我查询了表，然后使用游标迭代并插入到一个新表中。

我当前的表有很多很多列，我正试图使它扁平。可能透视。

RecordId    FirstName    LastName    Country     Grade
-------------------------------------------------------
1           Joe          Fresh       Canada      95
2           John         Smith       Canada      94

新表看起来像这样

RecordId    Key        Value
--------------------------------
1           FirstName   Joe
1           LastName    Fresh
1           Country     Canada
1           Grade       95
2           FirstName   John
2           LastName    Smith
2           Country     Canada
2           Grade       94

可以给我一些指导吗?

我尝试unpivot并能够使表变平，但我缺少RecordId列

select [Key], [Value]
from
(
SELECT * 
FROM test
) src
unpivot
(
[Value] for [Key] in ([RecordId],[FirstName],[LastName], [Country], [Grade])
)

Answer 1

是的，它是不旋转的。请在UNPIVOT操作之前将RecordId列添加到SELECT语句中，从而将其包含在UNPIVOT结果中。试试这个:

SELECT RecordId, [Key], [Value]
FROM
(
SELECT RecordId, FirstName, LastName, Country, Grade
FROM test
) src
UNPIVOT
(
[Value] FOR [Key] IN (FirstName, LastName, Country, Grade)
) unpvt;

Answer 2

我发现使用交叉应用通常更容易，并且允许更大的灵活性:

select t.RecordId, v.*
from t
cross apply(values
('FirstName', FirstName), 
('LastName', LastName), 
('Country', Country), 
('Grade', Cast(Grade as varchar(10)))
)v([key], [value]);