SQL在WHILE或FOR NEXT循环中生成列名

Question

我正在从查询中的20个列中选择不同的值，如下所示，并想知道是否可以使用循环以更整洁的方式完成这一任务？在阅读了一些关于类似主题的答案之后，似乎大家的共识是，要避免动态SQL？

在我的例子中，我只是希望有一个硬编码的“缺陷”，并将一个整数变量连接到每个循环的末尾，即1、2、3、4.20.这有可能吗？我当前的查询看起来像这样，看起来不太漂亮！

SELECT Defect1 AS HashKeys FROM VisionMachineResults
UNION
SELECT Defect2 FROM VisionMachineResults
UNION
SELECT Defect3 FROM VisionMachineResults
UNION
SELECT Defect4 FROM VisionMachineResults
UNION
SELECT Defect5 FROM VisionMachineResults
UNION
SELECT Defect6 FROM VisionMachineResults
UNION
SELECT Defect7 FROM VisionMachineResults
UNION
SELECT Defect8 FROM VisionMachineResults
UNION
SELECT Defect9 FROM VisionMachineResults
UNION
SELECT Defect10 FROM VisionMachineResults
UNION
SELECT Defect11 FROM VisionMachineResults
UNION
SELECT Defect12 FROM VisionMachineResults
UNION
SELECT Defect13 FROM VisionMachineResults
UNION
SELECT Defect14 FROM VisionMachineResults
UNION
SELECT Defect15 FROM VisionMachineResults
UNION
SELECT Defect16 FROM VisionMachineResults
UNION
SELECT Defect17 FROM VisionMachineResults
UNION
SELECT Defect18 FROM VisionMachineResults
UNION
SELECT Defect19 FROM VisionMachineResults
UNION
SELECT Defect20 FROM VisionMachineResults

Answer 1

你能试试使用UNPIVOT吗？

SELECT DISTINCT [value]
FROM VisionMachineResults
UNPIVOT
(
    [value] FOR [column] IN ([Defect1], [Defect2], ... [Defectn])
) UNPVT;