--> 测试数据:# if object_id('tempdb.dbo.#') is not null drop table # create table #(class_no int, cls float, sch float, [all] float, PH numeric(4,2), PL float) insert into # select 210201, 66.078431372549, 63.05, 58.49, 65.73, 50.28 union all select 210202, 58.989898989899, 63.05, 58.49, 65.73, 50.28 union all select 210203, 66, 63.05, 58.49, 65.73, 50.28 union all select 210204, 62.1705426356589, 63.05, 58.49, 65.73, 50.28 union all select 210205, 56.5000000000001, 63.05, 58.49, 65.73, 50.28select ltrim(class_no)class_no, cls from # union select 'sch', sch from # union select 'all', [all] from # union select 'PH', PH from # union select 'PL', PL from #/* class_no cls ------------ ---------------------- 210201 66.078431372549 210202 58.989898989899 210203 66 210204 62.1705426356589 210205 56.5000000000001 all 58.49 PH 65.73 PL 50.28 sch 63.05 */
无耻的借一下“SQLCenter”的环境if object_id('tempdb.dbo.#') is not null drop table # create table #(class_no int, cls float, sch float, [all] float, PH numeric(4,2), PL float) insert into # select 210201, 66.078431372549, 63.05, 58.49, 65.73, 50.28 union all select 210202, 58.989898989899, 63.05, 58.49, 65.73, 50.28 union all select 210203, 66, 63.05, 58.49, 65.73, 50.28 union all select 210204, 62.1705426356589, 63.05, 58.49, 65.73, 50.28 union all select 210205, 56.5000000000001, 63.05, 58.49, 65.73, 50.28select * from #select cast(class_no as varchar(10)) class_no,cast(cls as numeric(10,0)) as cls from # as a union all select top 1 'sch',cast(sch as numeric(10,2)) from # union all select top 1 'all',cast([all] as numeric(10,2)) from # union all select top 1 'PH',cast(ph as numeric(10,2) ) from # union all select top 1 'pl',cast(pl as numeric(10,2)) from #结果 210201 66.00 210202 59.00 210203 66.00 210204 62.00 210205 57.00 sch 63.05 all 58.49 PH 65.73 pl 50.28
if object_id('tempdb.dbo.#') is not null drop table #
create table #(class_no int, cls float, sch float, [all] float, PH numeric(4,2), PL float)
insert into #
select 210201, 66.078431372549, 63.05, 58.49, 65.73, 50.28 union all
select 210202, 58.989898989899, 63.05, 58.49, 65.73, 50.28 union all
select 210203, 66, 63.05, 58.49, 65.73, 50.28 union all
select 210204, 62.1705426356589, 63.05, 58.49, 65.73, 50.28 union all
select 210205, 56.5000000000001, 63.05, 58.49, 65.73, 50.28select ltrim(class_no)class_no, cls from #
union
select 'sch', sch from #
union
select 'all', [all] from #
union
select 'PH', PH from #
union
select 'PL', PL from #/*
class_no cls
------------ ----------------------
210201 66.078431372549
210202 58.989898989899
210203 66
210204 62.1705426356589
210205 56.5000000000001
all 58.49
PH 65.73
PL 50.28
sch 63.05
*/
create table #(class_no int, cls float, sch float, [all] float, PH numeric(4,2), PL float)
insert into #
select 210201, 66.078431372549, 63.05, 58.49, 65.73, 50.28 union all
select 210202, 58.989898989899, 63.05, 58.49, 65.73, 50.28 union all
select 210203, 66, 63.05, 58.49, 65.73, 50.28 union all
select 210204, 62.1705426356589, 63.05, 58.49, 65.73, 50.28 union all
select 210205, 56.5000000000001, 63.05, 58.49, 65.73, 50.28select * from #select cast(class_no as varchar(10)) class_no,cast(cls as numeric(10,0)) as cls from # as a
union all
select top 1 'sch',cast(sch as numeric(10,2)) from #
union all
select top 1 'all',cast([all] as numeric(10,2)) from #
union all
select top 1 'PH',cast(ph as numeric(10,2) ) from #
union all
select top 1 'pl',cast(pl as numeric(10,2)) from #结果
210201 66.00
210202 59.00
210203 66.00
210204 62.00
210205 57.00
sch 63.05
all 58.49
PH 65.73
pl 50.28