您可以使用UNPIVOT
对于像这样的一行,仅获取包含值的列
SELECT colvalue
FROM
(
SELECT *
FROM Table1
UNPIVOT INCLUDE NULLS
(
colvalue FOR cols IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)
)
);
示例输出:
| COLVALUE |
------------
| 1 |
| 2 |
| (null) |
|..........|
如果您需要包含数据透视表中的列名称的列,只需放弃外部选择
SELECT *
FROM Table1
UNPIVOT INCLUDE NULLS
(
colvalue FOR cols IN (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, ... col50)
);
示例输出:
| COLS | COLVALUE |
--------------------
| COL1 | 1 |
| COL2 | 2 |
| COL3 | (null) |
| ..... |......... |
Here is SQLFiddle http://sqlfiddle.com/#!4/1dc5a/2 demo