SELECT tt.val
FROM (SELECT 10 AS A, 'SomeThing' AS B UNION ALL SELECT 30, 'Other') t
OUTER APPLY (VALUES (CAST(t.A AS VARCHAR(10))), (t.B)) tt(val);
以上代码将表格中的A、B两列转成两行显示,对INT类型转换为其他类型。
也可以用UNION ALL来连接,但对列比较多的不友好,多次读取也可能影响性能(未实际测试)
相同数据类型的列转行也可以用UNPIVOT:
SELECT up.val, up.x
FROM (SELECT 10 AS A, 20 AS B UNION ALL SELECT 30, 40) t
UNPIVOT ( val
FOR x IN (A, B)) up;