查询结果是一行数据，包括日期类型，字符型和数字型，现想将一行数据显示成一列，用union all 不好使，求大神给方法。 2014-07-11 0 90%
------解决方案--------------------
注意日期部分会根据你本机区域语言可能有不同的结果
----------------------------------------------------------------
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-07-24 14:33:29
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([date] datetime,[zt_bll] int,[zt_mbz] int,[xz_bll] int,[xz_mbz] int,[hck_bll] int,[hck_mbz] int,[dg_bll] int,[dg_mbz] int,[jd_bll] int,[jd_mbz] int,[mk_bll] int,[mk_mbz] int,[bm_bll] int,[bm_mbz] int,[dc_bll] int,[dc_mbz] int,[mt_bll] int,[mt_mbz] int,[fj_bll] int,[fj_mbz] int,[a_bll] int,[a_mbz] int,[b_bll] int,[b_mbz] int,[mkl_bll] int,[mkl_mbz] int,[bml_bll] int,[bml_mbz] int,[dcl_bll] int,[dcl_mbz] int,[mtl_bll] int,[mtl_mbz] int,[fjl_bll] int,[fjl_mbz] int,[qcl_bll] int,[qcl_mbz] int,[tpv_bll] int,[tpv_mbz] int,[gj_bll] varchar(3),[gj_mbz] varchar(3),[ycl_bll] varchar(3),[ycl_mbz] varchar(3),[wg_bll] varchar(3),[wg_mbz] varchar(3),[gc_bll] varchar(3),[gc_mbz] varchar(3),[jz_scsl] int,[jz_blsl] int,[jz_bll] varchar(3),[jz_mbz] varchar(3),[mft_scsl] int,[mft_blsl] int,[mft_bll] int,[mft_mbz] int,[jzzj_jypc] int,[jzzj_blpc] int,[jzzj_bll] int,[jzzj_mbz] int,[jh_jypc] int,[jh_blpc] int,[jh_bll] int,[jh_mbz] int,[tpv_jypc] int,[tpv_blpc] int,[tpv1_bll] int,[tpv1_mbz] int,[bf_qcl] int,[bf_lh] int,[bf_jc] int,[bf_jh] int,[zxs_wtgj] int,[dq_wtgj] int,[aq_wtgj] int,[zxs_wtyf] int,[dq_wtyf] int,[aq_wtyf] int,[zxs_jytl] int,[dq_jytl] int,[aq_jytl] int,[zxs_glxt] int,[dq_glxt] int,[aq_glxt] int)
insert [huang]
select '2014-07-11',0,876,8130,76,97560,8766,1731707,0,170731,4,45935,2,432692,852,1024390,85285,19965,2852,1097560,855,1000000,3,1000000,123,0,745,0,575,0,75,0,575,0,76,0,74,0,74,'90%','90%','90%','90%','90%','90%','90%','90%',12312,123,'12%','12%',123,12,3123,123,123123,123,12,213,213,123,123,213,123,1123,23,123,123,123,123,123,123,1,2312,123,23,3123,12,123,123,3,1231,123
--------------开始查询--------------------------
declare @s nvarchar(max)
select @s=isnull(@s+' union all ','')+'select [col]=cast('+quotename(Name)+' as varchar(10)) '--isnull(@s+' union all ','') 去掉字符串@s中第一个union all+',[Score]='+quotename(name)
+' from [huang]'
from syscolumns where ID=object_id('[huang]') --and Name not in('Student')--排除不转换的列
order by Colid
EXEC ('select * from ('+@s+')t ')--增加一个排序
----------------结果----------------------------
/*
col
----------
07 11 2014
0
876
8130
76
97560
8766
1731707
0
170731
4
45935
2
432692
852
1024390
85285
19965
2852
1097560
855
1000000
3
1000000
123
0
745
0
575
0
75
0
575
0
76
0
74
0
74
90%
90%
90%
90%
90%
90%
90%
90%
12312
123
12%
12%
123
12
3123
123
123123
123
12
213
213
123
123
213
123
1123
23
123
123
123
123
123
123
1
2312
123
23
3123
12
123
123
3
1231
123
*/