尝试
select *
from yourTable
unpivot (
Value
for NewCol in (Value1, Value2, Value3,Value4, Value5)
) up
Id ColumnOne ColumnTwo ColumnThree
1 a b c
2 d e f
3 g h i
例子
-- Unpivoting with apply and VALUES.
WITH SampleData AS
(
/* This CTE returns 3 sample records.
*/
SELECT
cte.*
FROM
(
VALUES
(1, 'a', 'b', 'c'),
(2, 'd', 'e', 'f'),
(3, 'g', 'h', 'i')
) AS cte(Id, ColumnOne, ColumnTwo, ColumnThree)
)
SELECT
sd.Id,
ca.*
FROM
SampleData AS sd
CROSS APPLY
(
VALUES
(ColumnOne),
(ColumnTwo),
(ColumnThree)
) AS ca (ColumnFour)
;
Id ColumnFour
1 a
1 b
1 c
2 d
2 e
2 f
3 g
3 h
3 i
就个人而言,我更喜欢 @nazark 的方法。使用 UNPIVOT 运算符有助于其他人理解您代码的意图。如果那个答案对您有帮助,请 接受它。接受答案可以奖励贡献者的努力,并帮助其他遇到同样问题的人找到可行的答案。