1
/*
2
将表数据旋转90度(2007-11-19于海南三亚)
3
4将下表数据：
5A b c d e
6-------------------- ----------- ----------- ----------- -----------
7x 1 2 3 4
8y 5 6 7 8
9z 9 10 11 12
10
11转化成如下结果：
12a x y z
13-------------------- ---------- ---------- ----------
14b 1 5 9
15c 2 6 10
16d 3 7 11
17e 4 8 12
18
19
*/
20
21--生成测试数据
22create table test1(A varchar(20),b int,c int,d int,e int)
23insert into test1 select 'x',1,2 ,3 ,4
24insert into test1 select 'y',5,6 ,7 ,8
25insert into test1 select 'z',9,10,11,12
26go
27
28--生成中间数据表
29declare @s varchar(8000)
30set @s = 'create table test2(a varchar(20)'
31select @s = @s + ',' + A + ' varchar(10)' from test1
32set @s = @s + ')'
33exec(@s)
34print @s
35--借助中间表实现行列转换
36declare @name varchar(20)
37
38declare t_cursor cursor for
39select name from syscolumns
40where id=object_id('test1') and colid > 1 order by colid
41
42open t_cursor
43
44fetch next from t_cursor into @name
45
46while @@fetch_status = 0
47begin
48 exec('select ' + @name + ' as t into test3 from test1')
49 set @s='insert into test2 select ''' + @name + ''''
50 select @s = @s + ',''' + rtrim(t) + '''' from test3
51 exec(@s)
52 exec('drop table test3')
53 fetch next from t_cursor into @name
54end
55close t_cursor
56deallocate t_cursor
57
58--查看行列互换处理结果
59select * from test1
60select * from test2
61
62--删除表
63drop table test1
64drop table test2
65----------------------------------------------------------------------------
66/*固定的写法:*/
67select t1.* , t2.y , t3.z from
68(select a = 'b' , x = b from test1 where a = 'x') t1,
69(select a = 'b' , y = b from test1 where a = 'y') t2,
70(select a = 'b' , z = b from test1 where a = 'z') t3
71where t1.a = t2.a and t1.a = t2.a
72union all
73select t1.* , t2.y , t3.z from
74(select a = 'c' , x = c from test1 where a = 'x') t1,
75(select a = 'c' , y = c from test1 where a = 'y') t2,
76(select a = 'c' , z = c from test1 where a = 'z') t3
77where t1.a = t2.a and t1.a = t2.a
78union all
79select t1.* , t2.y , t3.z from
80(select a = 'd' , x = d from test1 where a = 'x') t1,
81(select a = 'd' , y = d from test1 where a = 'y') t2,
82(select a = 'd' , z = d from test1 where a = 'z') t3
83where t1.a = t2.a and t1.a = t2.a
84union all
85select t1.* , t2.y , t3.z from
86(select a = 'e' , x = e from test1 where a = 'x') t1,
87(select a = 'e' , y = e from test1 where a = 'y') t2,
88(select a = 'e' , z = e from test1 where a = 'z') t3
89where t1.a = t2.a and t1.a = t2.a
90
91----------------------------------------------------------------------------
92/*
93表tb,数据如下:
94项目种类 业绩 提成
95洗吹类 200 10
96外卖 100 5
97合计 300 15
98转换成:
99项目种类 洗吹类 外卖 合计
100业绩 200 100 300
101提成 10 5 15
102*/
103
104create table tb
105(
106 项目种类 varchar(10),
107 业绩 int,
108 提成 int
109)
110
111insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
112insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)
113insert into tb(项目种类,业绩,提成) values('合计' ,300,15)
114go
115
116select 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
117(
118 select 项目种类 = '业绩',
119 洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
120 外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,
121 合计 = case when 项目种类 = '合计' then 业绩 else 0 end
122 from tb
123union all
124 select 项目种类 = '提成' ,
125 洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,
126 外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,
127 合计 = case when 项目种类 = '合计' then 提成 else 0 end
128 from tb
129) m
130group by 项目种类
131order by 项目种类 desc
132
133drop table tb
134
135/*
136项目种类 洗吹类 外卖 合计
137-------- ----------- ----------- -----------
138业绩 200 100 300
139提成 10 5 15
140
141（所影响的行数为 2 行）
142*/
143
144--------------------------------------------------------------------------
145/*
146数据库中tb表格如下
147
148月份 工资 福利 奖金
1491月 100 200 300
1502月 110 210 310
1513月 120 220 320
1524月 130 230 330
153
154我想得到的结果是
155
156项目 1月 2月 3月 4月
157工资 100 110 120 130
158福利 200 210 220 230
159奖金 300 310 320 330
160
161就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？
162*/
163
164if exists (select * from dbo.sysobjects
165where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
166drop procedure [dbo].[p_zj]
167GO
168/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/
169
170create proc p_zj
171 @tbname sysname, --要处理的表名
172 @fdname sysname, --做为转换的列名
173 @new_fdname sysname='' --为转换后的列指定列名
174as
175declare @s1 varchar(8000) , @s2 varchar(8000),
176 @s3 varchar(8000) , @s4 varchar(8000),
177 @s5 varchar(8000) , @i varchar(10)
178select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
179select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
180 @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
181 else @new_fdname + '=' end + '''''' + name + '''''''',
182 @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
183 ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
184 @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
185 @s5 = @s5 + '+'' union all ''+@' + @i,
186 @i=cast(@i as int)+1
187from syscolumns
188where object_id(@tbname)=id and name<>@fdname
189
190select @s1=substring(@s1,2,8000),
191 @s2=substring(@s2,2,8000),
192 @s4=substring(@s4,2,8000),
193 @s5=substring(@s5,16,8000)
194exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
195exec(' + @s5 + ')')
196go
197
198--用上面的存储过程测试：
199
200create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
201insert Test
202select '1月',100,200,300 union all
203select '2月',110,210,310 union all
204select '3月',120,220,320 union all
205select '4月',130,230,330
206go
207
208exec p_zj 'Test', '月份' , '项目'
209
210drop table Test
211drop proc p_zj
212
213/*
214项目 1月 2月 3月 4月
215---- ----------- ----------- ----------- -----------
216福利 200 210 220 230
217工资 100 110 120 130
218奖金 300 310 320 330
219
220（所影响的行数为 3 行）
221*/
222
223/*
224静态写法(SQL2005)
225*/
226--测试环境
227create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
228insert Test
229select '1月',100,200,300 union all
230select '2月',110,210,310 union all
231select '3月',120,220,320 union all
232select '4月',130,230,330
233go
234--测试语句
235SELECT * FROM
236(
237 SELECT 考核月份,月份,金额 FROM
238 (SELECT 月份, 工资, 福利, 奖金 FROM Test) p
239 UNPIVOT
240 (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
241) T
242PIVOT
243(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt
244
245--测试结果
246
247/*
248考核月份 1月 2月 3月 4月
249------- ----- ----- ------ -------
250福利200210220230
251工资100110120130
252奖金300310320330
253*/
254
255--删除环境
256Drop table Test
257
/*
2
将表数据旋转90度(2007-11-19于海南三亚)3
4
将下表数据：5
A b c d e 6
-------------------- ----------- ----------- ----------- ----------- 7
x 1 2 3 48
y 5 6 7 89
z 9 10 11 1210
11
转化成如下结果：12
a x y z 13
-------------------- ---------- ---------- ---------- 14
b 1 5 915
c 2 6 1016
d 3 7 1117
e 4 8 1218
19
*/20
21
--生成测试数据22
create table test1(A varchar(20),b int,c int,d int,e int)23
insert into test1 select 'x',1,2 ,3 ,424
insert into test1 select 'y',5,6 ,7 ,825
insert into test1 select 'z',9,10,11,1226
go27
28
--生成中间数据表29
declare @s varchar(8000)30
set @s = 'create table test2(a varchar(20)'31
select @s = @s + ',' + A + ' varchar(10)' from test132
set @s = @s + ')'33
exec(@s)34
print @s35
--借助中间表实现行列转换36
declare @name varchar(20)37
38
declare t_cursor cursor for 39
select name from syscolumns 40
where id=object_id('test1') and colid > 1 order by colid41
42
open t_cursor43
44
fetch next from t_cursor into @name45
46
while @@fetch_status = 047
begin48
exec('select ' + @name + ' as t into test3 from test1')49
set @s='insert into test2 select ''' + @name + ''''50
select @s = @s + ',''' + rtrim(t) + '''' from test351
exec(@s)52
exec('drop table test3')53
fetch next from t_cursor into @name54
end55
close t_cursor56
deallocate t_cursor57
58
--查看行列互换处理结果59
select * from test160
select * from test261
62
--删除表63
drop table test164
drop table test265
----------------------------------------------------------------------------66
/*固定的写法:*/67
select t1.* , t2.y , t3.z from68
(select a = 'b' , x = b from test1 where a = 'x') t1, 69
(select a = 'b' , y = b from test1 where a = 'y') t2,70
(select a = 'b' , z = b from test1 where a = 'z') t371
where t1.a = t2.a and t1.a = t2.a72
union all73
select t1.* , t2.y , t3.z from74
(select a = 'c' , x = c from test1 where a = 'x') t1, 75
(select a = 'c' , y = c from test1 where a = 'y') t2,76
(select a = 'c' , z = c from test1 where a = 'z') t377
where t1.a = t2.a and t1.a = t2.a78
union all79
select t1.* , t2.y , t3.z from80
(select a = 'd' , x = d from test1 where a = 'x') t1, 81
(select a = 'd' , y = d from test1 where a = 'y') t2,82
(select a = 'd' , z = d from test1 where a = 'z') t383
where t1.a = t2.a and t1.a = t2.a84
union all85
select t1.* , t2.y , t3.z from86
(select a = 'e' , x = e from test1 where a = 'x') t1, 87
(select a = 'e' , y = e from test1 where a = 'y') t2,88
(select a = 'e' , z = e from test1 where a = 'z') t389
where t1.a = t2.a and t1.a = t2.a90
91
----------------------------------------------------------------------------92
/*93
表tb,数据如下:94
项目种类 业绩 提成95
洗吹类 200 1096
外卖 100 597
合计 300 1598
转换成:99
项目种类 洗吹类 外卖 合计100
业绩 200 100 300101
提成 10 5 15102
*/103
104
create table tb105
(106
项目种类 varchar(10),107
业绩 int,108
提成 int109
)110
111
insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)112
insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)113
insert into tb(项目种类,业绩,提成) values('合计' ,300,15)114
go115
116
select 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from117
(118
select 项目种类 = '业绩',119
洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,120
外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,121
合计 = case when 项目种类 = '合计' then 业绩 else 0 end122
from tb123
union all124
select 项目种类 = '提成' ,125
洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,126
外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,127
合计 = case when 项目种类 = '合计' then 提成 else 0 end128
from tb129
) m130
group by 项目种类131
order by 项目种类 desc132
133
drop table tb134
135
/*136
项目种类 洗吹类 外卖 合计 137
-------- ----------- ----------- ----------- 138
业绩 200 100 300139
提成 10 5 15140
141
（所影响的行数为 2 行）142
*/143
144
--------------------------------------------------------------------------145
/*146
数据库中tb表格如下147
148
月份 工资 福利 奖金149
1月 100 200 300150
2月 110 210 310151
3月 120 220 320152
4月 130 230 330153
154
我想得到的结果是155
156
项目 1月 2月 3月 4月157
工资 100 110 120 130158
福利 200 210 220 230159
奖金 300 310 320 330160
161
就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？162
*/163
164
if exists (select * from dbo.sysobjects165
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)166
drop procedure [dbo].[p_zj]167
GO168
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/169
170
create proc p_zj171
@tbname sysname, --要处理的表名172
@fdname sysname, --做为转换的列名173
@new_fdname sysname='' --为转换后的列指定列名174
as175
declare @s1 varchar(8000) , @s2 varchar(8000),176
@s3 varchar(8000) , @s4 varchar(8000),177
@s5 varchar(8000) , @i varchar(10)178
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'179
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',180
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''181
else @new_fdname + '=' end + '''''' + name + '''''''',182
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 183
']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',184
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,185
@s5 = @s5 + '+'' union all ''+@' + @i,186
@i=cast(@i as int)+1187
from syscolumns188
where object_id(@tbname)=id and name<>@fdname189
190
select @s1=substring(@s1,2,8000),191
@s2=substring(@s2,2,8000),192
@s4=substring(@s4,2,8000),193
@s5=substring(@s5,16,8000)194
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '195
exec(' + @s5 + ')')196
go197
198
--用上面的存储过程测试：199
200
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)201
insert Test 202
select '1月',100,200,300 union all203
select '2月',110,210,310 union all204
select '3月',120,220,320 union all205
select '4月',130,230,330206
go207
208
exec p_zj 'Test', '月份' , '项目'209
210
drop table Test211
drop proc p_zj212
213
/*214
项目 1月 2月 3月 4月 215
---- ----------- ----------- ----------- ----------- 216
福利 200 210 220 230217
工资 100 110 120 130218
奖金 300 310 320 330219
220
（所影响的行数为 3 行）221
*/222
223
/*224
静态写法(SQL2005)225
*/226
--测试环境227
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)228
insert Test229
select '1月',100,200,300 union all230
select '2月',110,210,310 union all231
select '3月',120,220,320 union all232
select '4月',130,230,330233
go234
--测试语句235
SELECT * FROM 236
(237
SELECT 考核月份,月份,金额 FROM 238
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p239
UNPIVOT240
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt241
) T242
PIVOT243
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt244
245
--测试结果246
247
/*248
考核月份 1月 2月 3月 4月249
------- ----- ----- ------ -------250
福利200210220230251
工资100110120130252
奖金300310320330253
*/254
255
--删除环境256
Drop table Test257
浙公网安备 33010602011771号