我发现在某些情况下,将UNPIVOT结果与从INFORMATION_SCHEMA方便地提取的完整字段列表进行左外连接是解决这个问题的实用答案。
CREATE TABLE _t1(name varchar(20),object_id varchar(20),principal_id varchar(20),schema_id varchar(20),parent_object_id varchar(20),type varchar(20),type_desc varchar(20),create_date varchar(20),modify_date varchar(20),is_ms_shipped varchar(20),is_published varchar(20),is_schema_published varchar(20))
INSERT INTO _t1 SELECT 'blah1', 3, NULL, 4, 0, 'blah2', 'blah3', '20100402 16:59:23.267', NULL, 1, 0, 0
select c.COLUMN_NAME, Value
from INFORMATION_SCHEMA.COLUMNS c
left join (
select * from _t1
) q1
unpivot (Value for COLUMN_NAME in (name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published)
) t on t.COLUMN_NAME = c.COLUMN_NAME
where c.TABLE_NAME = '_t1'
</pre>
输出结果如下:
+----------------------+-----------------------+
| COLUMN_NAME | Value |
+----------------------+-----------------------+
| name | blah1 |
| object_id | 3 |
| principal_id | NULL | <======(这里应该添加解释:<代表小于符号,该列数据为空)
| schema_id | 4 |
| parent_object_id | 0 |
| type | blah2 |
| type_desc | blah3 |
| create_date | 20100402 16:59:23.26 |
| modify_date | NULL | <======(同上)
| is_ms_shipped | 1 |
| is_published | 0 |
| is_schema_published | 0 |
+----------------------+-----------------------+