【精】行列互换通用存储过程

windy8848

--创建测试数据
create table TestData(姓名 varchar(10), 语文 dec(14,2), 数学 dec(14,2), 英语 dec(14,2), 物理 dec(14,2), 化学 dec(14,2))
insert TestData
select '张三',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))
union all
select '李四',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))
union all
select '王五',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))
union all
select '赵六',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))
union all
select '陈七',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))
union all
select '孙八',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))
union all
select '刘二',cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2)),cast(RAND()*100 AS dec(14,2))

SELECT * FROM TestData
姓名 语文 数学 英语 物理 化学
张三 75.29 81.49 80.96 46.59 5.38
李四 66.20 98.39 10.37 6.96 79.53
王五 22.40 53.79 19.02 85.18 43.37
赵六 77.53 67.82 57.42 39.98 44.37
陈七 9.96 55.43 3.71 80.10 56.30
孙八 50.98 7.03 23.16 95.61 12.28
刘二 45.64 10.48 30.66 48.60 13.68

--测试转换结果
proc_hanglie 'TestData','姓名','科目'

行列转换通用存储过程下载： 行列互换通用存储过程.rar (795 Bytes, 下载次数: 5, 售价: 10 金币) 充值金币->

2012-8-17 22:27 上传

点击文件名下载附件

科目 张三 李四 王五 赵六 陈七 孙八 刘二
语文 60.18 67.88 41.37 87.81 31.68 93.19 7.49
数学 4.84 80.32 45.46 59.11 44.10 99.18 0.98
英语 71.65 70.90 81.85 31.19 74.37 39.56 32.34
物理 92.32 41.68 15.45 56.33 75.35 17.50 19.95
化学 66.42 49.00 36.14 11.65 5.62 1.18 61.61

DROP TABLE TestData

skchyun

应该是过程很好用

zzh0623

这个查询真好呀