SQL大圣之路笔记——SQL 行转列，列转行

　　/*普通行列转换 2 (爱新觉罗.毓华 2007-11-18于海南三亚) 3 4 假设有张学生成绩表(tb)如下: 5 Name Subject Result 6 张三 语文　　74 7 张三 数学　　83 8 张三 物理　　93 9 李四 语文　　74 10 李四 数学　　84 11 李四 物理　　94 12 */ 13 14 ------------------------------------------------------------------------- 15 /* 16 想变成 17 姓名 语文 数学 物理 18 ---------- ----------- ----------- ----------- 19 李四 74 84 94 20 张三 74 83 93 21 */ 22 23 create table tb 24 ( 25 Name varchar(10) , 26 Subject varchar(10) , 27 Result int 28 ) 29 30 insert into tb(Name , Subject , Result) values(‘张三‘ , ‘语文‘ , 74) 31 insert into tb(Name , Subject , Result) values(‘张三‘ , ‘数学‘ , 83) 32 insert into tb(Name , Subject , Result) values(‘张三‘ , ‘物理‘ , 93) 33 insert into tb(Name , Subject , Result) values(‘李四‘ , ‘语文‘ , 74) 34 insert into tb(Name , Subject , Result) values(‘李四‘ , ‘数学‘ , 84) 35 insert into tb(Name , Subject , Result) values(‘李四‘ , ‘物理‘ , 94) 36 go 37 38 --静态SQL,指subject只有语文、数学、物理这三门课程。 39 select name 姓名, 40 max(case subject when ‘语文‘ then result else 0 end) 语文, 41 max(case subject when ‘数学‘ then result else 0 end) 数学, 42 max(case subject when ‘物理‘ then result else 0 end) 物理 43 from tb 44 group by name 45 /* 46 姓名 语文 数学 物理 47 ---------- ----------- ----------- ----------- 48 李四 74 84 94 49 张三 74 83 93 50 */ 51 52 --动态SQL,指subject不止语文、数学、物理这三门课程。 53 declare @sql varchar(8000) 54 set @sql = ‘select Name as ‘ + ‘姓名‘ 55 select @sql = @sql + ‘ , max(case Subject when ‘‘‘ + Subject + ‘‘‘ then Result else 0 end) [‘ + Subject + ‘]‘ 56 from (select distinct Subject from tb) as a 57 set @sql = @sql + ‘ from tb group by name‘ 58 exec(@sql) 59 /* 60 姓名 数学 物理 语文 61 ---------- ----------- ----------- ----------- 62 李四 84 94 74 63 张三 83 93 74 64 */ 65 66 ------------------------------------------------------------------- 67 /*加个平均分，总分 68 姓名 语文 数学 物理 平均分 总分 69 ---------- ----------- ----------- ----------- -------------------- ----------- 70 李四 74 84 94 84.00 252 71 张三 74 83 93 83.33 250 72 */ 73 74 --静态SQL,指subject只有语文、数学、物理这三门课程。 75 select name 姓名, 76 max(case subject when ‘语文‘ then result else 0 end) 语文, 77 max(case subject when ‘数学‘ then result else 0 end) 数学, 78 max(case subject when ‘物理‘ then result else 0 end) 物理, 79 cast(avg(result*1.0) as decimal(18,2)) 平均分, 80 sum(result) 总分 81 from tb 82 group by name 83 /* 84 姓名 语文 数学 物理 平均分 总分 85 ---------- ----------- ----------- ----------- -------------------- ----------- 86 李四 74 84 94 84.00 252 87 张三 74 83 93 83.33 250 88 */ 89 90 --动态SQL,指subject不止语文、数学、物理这三门课程。 91 declare @sql1 varchar(8000) 92 set @sql1 = ‘select Name as ‘ + ‘姓名‘ 93 select @sql1 = @sql1 + ‘ , max(case Subject when ‘‘‘ + Subject + ‘‘‘ then Result else 0 end) [‘ + Subject + ‘]‘ 94 from (select distinct Subject from tb) as a 95 set @sql1 = @sql1 + ‘ , cast(avg(result*1.0) as decimal(18,2)) 平均分,sum(result) 总分 from tb group by name‘ 96 exec(@sql1) 97 /* 98 姓名 数学 物理 语文 平均分 总分 99 ---------- ----------- ----------- ----------- -------------------- ----------- 100 李四 84 94 74 84.00 252 101 张三 83 93 74 83.33 250 102 */ 103 104 drop table tb 105 106 --------------------------------------------------------- 107 --------------------------------------------------------- 108 /* 109 如果上述两表互相换一下：即 110 111 姓名 语文 数学 物理 112 张三 74　　83　　93 113 李四 74　　84　　94 114 115 想变成 116 Name Subject Result 117 ---------- ------- ----------- 118 李四 语文 74 119 李四 数学 84 120 李四 物理 94 121 张三 语文 74 122 张三 数学 83 123 张三 物理 93 124 */ 125 126 create table tb1 127 ( 128 姓名 varchar(10) , 129 语文 int , 130 数学 int , 131 物理 int 132 ) 133 134 insert into tb1(姓名 , 语文 , 数学 , 物理) values(‘张三‘,74,83,93) 135 insert into tb1(姓名 , 语文 , 数学 , 物理) values(‘李四‘,74,84,94) 136 137 select * from 138 ( 139 select 姓名 as Name , Subject = ‘语文‘ , Result = 语文 from tb1 140 union all 141 select 姓名 as Name , Subject = ‘数学‘ , Result = 数学 from tb1 142 union all 143 select 姓名 as Name , Subject = ‘物理‘ , Result = 物理 from tb1 144 ) t 145 order by name , case Subject when ‘语文‘ then 1 when ‘数学‘ then 2 when ‘物理‘ then 3 when ‘总分‘ then 4 end 146 147 -------------------------------------------------------------------- 148 /*加个平均分,总分 149 Name Subject Result 150 ---------- ------- -------------------- 151 李四 语文 74.00 152 李四 数学 84.00 153 李四 物理 94.00 154 李四 平均分 84.00 155 李四 总分 252.00 156 张三 语文 74.00 157 张三 数学 83.00 158 张三 物理 93.00 159 张三 平均分 83.33 160 张三 总分 250.00 161 */ 162 163 select * from 164 ( 165 select 姓名 as Name , Subject = ‘语文‘ , Result = 语文 from tb1 166 union all 167 select 姓名 as Name , Subject = ‘数学‘ , Result = 数学 from tb1 168 union all 169 select 姓名 as Name , Subject = ‘物理‘ , Result = 物理 from tb1 170 union all 171 select 姓名 as Name , Subject = ‘平均分‘ , Result = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb1 172 union all 173 select 姓名 as Name , Subject = ‘总分‘ , Result = 语文 + 数学 + 物理 from tb1 174 ) t 175 order by name , case Subject when ‘语文‘ then 1 when ‘数学‘ then 2 when ‘物理‘ then 3 when ‘平均分‘ then 4 when ‘总分‘ then 5 end 176 177 drop table tb1

 

SQL大圣之路笔记——SQL 行转列，列转行

标签：