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