/*--------------------------------------------------------------------------* 表格旋转/转置: 好像也叫交叉表* 说明 不支持下列数据类型: image, text, ntext, hierarchyid, geometry, geography. 2000 好像没有 fn_varbintohexstr, 所以不支持 varbinary, binary, timestamp,
要扩展自己写 bin2str 函数. 2008 的 varbinary/binary 可以直接 convert, 详细参考联机/MSDN, 但懒得判断
版本了, 一律用 fn_varbintohexstr. 2000 内层受长度 8000 的限制, 某列数据超过 8000 长度肯定报错.
2005/2008 检测用 varchar(max) 轴向列转 sysname, 超过 128 截断. 其它列除有限的几个要显式转换的数据类型,
一律用 rtrim() 隐式转换, 具体看代码. 有处理 NULL 值, 不至于被一个 NULL 玩死.* 作者 这些东西毫无技术含量可言, 不敢言称作者, 以免贻笑大方. PS: 写着玩/不维护/不扩展, BUG 有时间就跟进无时间见谅. 相信除了长度限制的
硬伤, 其它 BUG 的可能性不大.--------------------------------------------------------------------------*/
CREATE PROCEDURE p_rotate
(
@table sysname, -- 表/视图
@axis sysname = null, -- 轴, 旋转后作为字段名, 默认第1列
@rename sysname = null, -- 重命名轴
@style int = 121 -- 日期时间转换样式
)
ASSET NOCOUNT ONif object_id(@table) is null return -- 不废话declare @inner varchar(8000) -- 定义内层 exec 变量
declare @first varchar(8000) -- 每行数据的第一列 即原字段名变成第1列
declare @rows varchar(8000) -- 读取每列数据作为行数据
declare @union varchar(8000) -- 每行数据 union all
declare @max varchar(10)
declare @type intselect @axis = isnull(@axis, (select name from syscolumns where id=object_id(@table) and colid=1))
select @type = xtype from syscolumns where id=object_id(@table) and name=@axisif @type in (34,35,99,240) -- image,text,ntext,hierarchyid,geometry,geography
or @@version not like '%Server 200[58]%' and @type in (165,173,189) -- varbinary,binary,timestamp
begin
select name from systypes where xtype = @type
return
endselect @rename = isnull(@rename, @axis), @max = case when @@version like '%Server 200[58]%' then 'max' else '8000' end-- 构造内层 exec
select
@inner = isnull(@inner+',','')+'@'+ltrim(colid)+' varchar('+@max+')',
@first = isnull(@first+',','')+'@'+ltrim(colid)+'=''select ['+@rename+']='''''+name+'''''''',
@rows = isnull(@rows,'')+char(13)+char(10)+'select @'+ltrim(colid)+'=@'+ltrim(colid)+'+'',[''+isnull('+
case
when @type = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+@axis+']))' -- timestamp
when @type in (165,173) then 'left(master.sys.fn_varbintohexstr(['+@axis+']),128)' -- varbinary,binary
when @type in (175,239) then 'rtrim(convert(sysname,['+@axis+']))' -- char,nchar
when @type in (40,41,42,43,58,61) then 'convert(sysname,['+@axis+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime
else 'convert(sysname,['+@axis+'])'
end+',''NULL'')+'']=''+isnull(quotename('+
case
when xtype = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+name+']))' -- timestamp
when xtype in (165,173) then 'master.sys.fn_varbintohexstr(['+name+'])' -- varbinary,binary
--when xtype in (60,122) then 'convert(varchar(50),['+name+'],2)' -- money,smallmoney -- 需要精细控制类型转换这里添加
when xtype in (40,41,42,43,58,61) then 'convert(varchar(50),['+name+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime
when xtype in (98,241) then 'convert(varchar('+@max+'),['+name+'])' -- sql_variant,xml
else 'rtrim(['+name+'])'
end+', char(39)),''null'') from ['+@table+']',
@union = isnull(@union+'+'' union all ''+','')+'@'+ltrim(colid)
from syscolumns
where id=object_id(@table) and name<>@axis and (xtype not in (34,35,99,165,173,189,240) or @@version like '%Server 200[58]%' and xtype not in (34,35,99,240))
order by colid-- print/exec
exec('declare '+@inner+'
select '+@first+@rows+'
exec('+@union+')')SET NOCOUNT OFF用这个贴的数据作为例子：
http://topic.csdn.net/u/20101026/00/57b8a119-3913-427b-a2a7-0dc167ee7c0e.html
要扩展自己写 bin2str 函数. 2008 的 varbinary/binary 可以直接 convert, 详细参考联机/MSDN, 但懒得判断
版本了, 一律用 fn_varbintohexstr. 2000 内层受长度 8000 的限制, 某列数据超过 8000 长度肯定报错.
2005/2008 检测用 varchar(max) 轴向列转 sysname, 超过 128 截断. 其它列除有限的几个要显式转换的数据类型,
一律用 rtrim() 隐式转换, 具体看代码. 有处理 NULL 值, 不至于被一个 NULL 玩死.* 作者 这些东西毫无技术含量可言, 不敢言称作者, 以免贻笑大方. PS: 写着玩/不维护/不扩展, BUG 有时间就跟进无时间见谅. 相信除了长度限制的
硬伤, 其它 BUG 的可能性不大.--------------------------------------------------------------------------*/
CREATE PROCEDURE p_rotate
(
@table sysname, -- 表/视图
@axis sysname = null, -- 轴, 旋转后作为字段名, 默认第1列
@rename sysname = null, -- 重命名轴
@style int = 121 -- 日期时间转换样式
)
ASSET NOCOUNT ONif object_id(@table) is null return -- 不废话declare @inner varchar(8000) -- 定义内层 exec 变量
declare @first varchar(8000) -- 每行数据的第一列 即原字段名变成第1列
declare @rows varchar(8000) -- 读取每列数据作为行数据
declare @union varchar(8000) -- 每行数据 union all
declare @max varchar(10)
declare @type intselect @axis = isnull(@axis, (select name from syscolumns where id=object_id(@table) and colid=1))
select @type = xtype from syscolumns where id=object_id(@table) and name=@axisif @type in (34,35,99,240) -- image,text,ntext,hierarchyid,geometry,geography
or @@version not like '%Server 200[58]%' and @type in (165,173,189) -- varbinary,binary,timestamp
begin
select name from systypes where xtype = @type
return
endselect @rename = isnull(@rename, @axis), @max = case when @@version like '%Server 200[58]%' then 'max' else '8000' end-- 构造内层 exec
select
@inner = isnull(@inner+',','')+'@'+ltrim(colid)+' varchar('+@max+')',
@first = isnull(@first+',','')+'@'+ltrim(colid)+'=''select ['+@rename+']='''''+name+'''''''',
@rows = isnull(@rows,'')+char(13)+char(10)+'select @'+ltrim(colid)+'=@'+ltrim(colid)+'+'',[''+isnull('+
case
when @type = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+@axis+']))' -- timestamp
when @type in (165,173) then 'left(master.sys.fn_varbintohexstr(['+@axis+']),128)' -- varbinary,binary
when @type in (175,239) then 'rtrim(convert(sysname,['+@axis+']))' -- char,nchar
when @type in (40,41,42,43,58,61) then 'convert(sysname,['+@axis+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime
else 'convert(sysname,['+@axis+'])'
end+',''NULL'')+'']=''+isnull(quotename('+
case
when xtype = 189 then 'master.sys.fn_varbintohexstr(convert(binary(8),['+name+']))' -- timestamp
when xtype in (165,173) then 'master.sys.fn_varbintohexstr(['+name+'])' -- varbinary,binary
--when xtype in (60,122) then 'convert(varchar(50),['+name+'],2)' -- money,smallmoney -- 需要精细控制类型转换这里添加
when xtype in (40,41,42,43,58,61) then 'convert(varchar(50),['+name+'],'+ltrim(@style)+')' -- date,time,datetime2,datetimeoffset,smalldatetime,datetime
when xtype in (98,241) then 'convert(varchar('+@max+'),['+name+'])' -- sql_variant,xml
else 'rtrim(['+name+'])'
end+', char(39)),''null'') from ['+@table+']',
@union = isnull(@union+'+'' union all ''+','')+'@'+ltrim(colid)
from syscolumns
where id=object_id(@table) and name<>@axis and (xtype not in (34,35,99,165,173,189,240) or @@version like '%Server 200[58]%' and xtype not in (34,35,99,240))
order by colid-- print/exec
exec('declare '+@inner+'
select '+@first+@rows+'
exec('+@union+')')SET NOCOUNT OFF用这个贴的数据作为例子：
http://topic.csdn.net/u/20101026/00/57b8a119-3913-427b-a2a7-0dc167ee7c0e.html
if object_id('student') is not null drop table student
create table student(姓名 varchar(8), 学号 int, 专业 varchar(8), 性别 varchar(8), 高数 int, 化学 int, 英语 int, 物理 int, 总分 float, 个人平均分 float, 名次 int)
insert into student
select '学生壬', 1009, '热能', '女', 89, 93, 84, 90, 356.00, 89.00, 1 union all
select '学生甲', 1001, '冶金', '男', 88, 87, 78, 98, 351.00, 87.75, 2 union all
select '学生癸', 1010, '热能', '女', 83, 91, 85, 89, 348.00, 87.00, 3 union all
select '学生丙', 1003, '冶金', '女', 97, 90, 70, 89, 346.00, 86.50, 4 union all
select '学生戊', 1005, '冶金', '男', 91, 99, 69, 81, 340.00, 85.00, 5 union all
select '学生寅', 1013, '机械', '女', 90, 80, 83, 76, 329.00, 82.25, 6 union all
select '学生卯', 1014, '机械', '男', 81, 92, 88, 62, 323.00, 80.75, 7 union all
select '学生辛', 1008, '热能', '女', 70, 80, 80, 84, 314.00, 78.50, 8 union all
select '学生辰', 1015, '机械', '男', 83, 91, 74, 65, 313.00, 78.25, 9 union all
select '学生丁', 1004, '冶金', '女', 79, 69, 83, 78, 309.00, 77.25, 10 union all
select '学生丑', 1012, '机械', '男', 92, 70, 77, 60, 299.00, 74.75, 11 union all
select '学生庚', 1007, '热能', '男', 76, 86, 59, 74, 295.00, 73.75, 12 union all
select '学生子', 1011, '机械', '男', 69, 84, 71, 71, 295.00, 73.75, 13 union all
select '学生乙', 1002, '冶金', '男', 85, 79, 72, 57, 293.00, 73.25, 14 union all
select '学生己', 1006, '热能', '男', 85, 73, 66, 69, 293.00, 73.25, 15select * from student
/*
姓名 学号 专业 性别 高数 化学 英语 物理 总分 个人平均分 名次
-------- ----------- -------- -------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
学生壬 1009 热能 女 89 93 84 90 356 89 1
学生甲 1001 冶金 男 88 87 78 98 351 87.75 2
学生癸 1010 热能 女 83 91 85 89 348 87 3
学生丙 1003 冶金 女 97 90 70 89 346 86.5 4
学生戊 1005 冶金 男 91 99 69 81 340 85 5
学生寅 1013 机械 女 90 80 83 76 329 82.25 6
学生卯 1014 机械 男 81 92 88 62 323 80.75 7
学生辛 1008 热能 女 70 80 80 84 314 78.5 8
学生辰 1015 机械 男 83 91 74 65 313 78.25 9
学生丁 1004 冶金 女 79 69 83 78 309 77.25 10
学生丑 1012 机械 男 92 70 77 60 299 74.75 11
学生庚 1007 热能 男 76 86 59 74 295 73.75 12
学生子 1011 机械 男 69 84 71 71 295 73.75 13
学生乙 1002 冶金 男 85 79 72 57 293 73.25 14
学生己 1006 热能 男 85 73 66 69 293 73.25 15
*/-- 转置
exec p_rotate 'student'/*
姓名 学生壬 学生甲 学生癸 学生丙 学生戊 学生寅 学生卯 学生辛 学生辰 学生丁 学生丑 学生庚 学生子 学生乙 学生己
---------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
学号 1009 1001 1010 1003 1005 1013 1014 1008 1015 1004 1012 1007 1011 1002 1006
专业 热能 冶金 热能 冶金 冶金 机械 机械 热能 机械 冶金 机械 热能 机械 冶金 热能
性别 女 男 女 女 男 女 男 女 男 女 男 男 男 男 男
高数 89 88 83 97 91 90 81 70 83 79 92 76 69 85 85
化学 93 87 91 90 99 80 92 80 91 69 70 86 84 79 73
英语 84 78 85 70 69 83 88 80 74 83 77 59 71 72 66
物理 90 98 89 89 81 76 62 84 65 78 60 74 71 57 69
总分 356 351 348 346 340 329 323 314 313 309 299 295 295 293 293
个人平均分 89 87.75 87 86.5 85 82.25 80.75 78.5 78.25 77.25 74.75 73.75 73.75 73.25 73.25
名次 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
*/
其实我也有一个方法一直没和大家分享大概1秒钟就可以...就是把显示器倒90度 恩恩恩
将表数据旋转90度(2007-11-19于海南三亚)将下表数据：
A b c d e
-------------------- ----------- ----------- ----------- -----------
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12转化成如下结果：
a x y z
-------------------- ---------- ---------- ----------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12*/--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
go--生成中间数据表
declare @s varchar(8000)
set @s = 'create table test2(a varchar(20)'
select @s = @s + ',' + A + ' varchar(10)' from test1
set @s = @s + ')'
exec(@s)
print @s
--借助中间表实现行列转换
declare @name varchar(20)declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid > 1 order by colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status = 0
begin
exec('select ' + @name + ' as t into test3 from test1')
set @s='insert into test2 select ''' + @name + ''''
select @s = @s + ',''' + rtrim(t) + '''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor--查看行列互换处理结果
select * from test1
select * from test2--删除表
drop table test1
drop table test2
----------------------------------------------------------------------------
/*固定的写法:*/
select t1.* , t2.y , t3.z from
(select a = 'b' , x = b from test1 where a = 'x') t1,
(select a = 'b' , y = b from test1 where a = 'y') t2,
(select a = 'b' , z = b from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'c' , x = c from test1 where a = 'x') t1,
(select a = 'c' , y = c from test1 where a = 'y') t2,
(select a = 'c' , z = c from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'd' , x = d from test1 where a = 'x') t1,
(select a = 'd' , y = d from test1 where a = 'y') t2,
(select a = 'd' , z = d from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a
union all
select t1.* , t2.y , t3.z from
(select a = 'e' , x = e from test1 where a = 'x') t1,
(select a = 'e' , y = e from test1 where a = 'y') t2,
(select a = 'e' , z = e from test1 where a = 'z') t3
where t1.a = t2.a and t1.a = t2.a----------------------------------------------------------------------------
/*
表tb,数据如下:
项目种类 业绩 提成
洗吹类 200 10
外卖 100 5
合计 300 15
转换成:
项目种类 洗吹类 外卖 合计
业绩 200 100 300
提成 10 5 15
*/create table tb
(
项目种类 varchar(10),
业绩 int,
提成 int
)insert into tb(项目种类,业绩,提成) values('洗吹类',200,10)
insert into tb(项目种类,业绩,提成) values('外卖' ,100,5)
insert into tb(项目种类,业绩,提成) values('合计' ,300,15)
goselect 项目种类,sum(洗吹类) as 洗吹类 , sum(外卖) as 外卖 , sum(合计) as 合计 from
(
select 项目种类 = '业绩',
洗吹类 = case when 项目种类 = '洗吹类' then 业绩 else 0 end,
外卖 = case when 项目种类 = '外卖' then 业绩 else 0 end,
合计 = case when 项目种类 = '合计' then 业绩 else 0 end
from tb
union all
select 项目种类 = '提成' ,
洗吹类 = case when 项目种类 = '洗吹类' then 提成 else 0 end,
外卖 = case when 项目种类 = '外卖' then 提成 else 0 end,
合计 = case when 项目种类 = '合计' then 提成 else 0 end
from tb
) m
group by 项目种类
order by 项目种类 descdrop table tb/*
项目种类 洗吹类 外卖 合计
-------- ----------- ----------- -----------
业绩 200 100 300
提成 10 5 15（所影响的行数为 2 行）
*/--------------------------------------------------------------------------
/*
数据库中tb表格如下
月份 工资 福利 奖金
1月 100 200 300
2月 110 210 310
3月 120 220 320
4月 130 230 330我想得到的结果是项目 1月 2月 3月 4月
工资 100 110 120 130
福利 200 210 220 230
奖金 300 310 320 330就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？
*/if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_zj]
GO
/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/create proc p_zj
@tbname sysname, --要处理的表名
@fdname sysname, --做为转换的列名
@new_fdname sysname='' --为转换后的列指定列名
as
declare @s1 varchar(8000) , @s2 varchar(8000),
@s3 varchar(8000) , @s4 varchar(8000),
@s5 varchar(8000) , @i varchar(10)
select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'
select @s1 = @s1 + ',@' + @i + ' varchar(8000)',
@s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''
else @new_fdname + '=' end + '''''' + name + '''''''',
@s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname +
']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',
@s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,
@s5 = @s5 + '+'' union all ''+@' + @i,
@i=cast(@i as int)+1
from syscolumns
where object_id(@tbname)=id and name<>@fdnameselect @s1=substring(@s1,2,8000),
@s2=substring(@s2,2,8000),
@s4=substring(@s4,2,8000),
@s5=substring(@s5,16,8000)
exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '
exec(' + @s5 + ')')
go--用上面的存储过程测试：create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
goexec p_zj 'Test', '月份' , '项目'drop table Test
drop proc p_zj/*
项目 1月 2月 3月 4月
---- ----------- ----------- ----------- -----------
福利 200 210 220 230
工资 100 110 120 130
奖金 300 310 320 330（所影响的行数为 3 行）
*//*
静态写法(SQL2005)
*/
--测试环境
create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)
insert Test
select '1月',100,200,300 union all
select '2月',110,210,310 union all
select '3月',120,220,320 union all
select '4月',130,230,330
go
--测试语句
SELECT * FROM
(
SELECT 考核月份,月份,金额 FROM
(SELECT 月份, 工资, 福利, 奖金 FROM Test) p
UNPIVOT
(金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt
) T
PIVOT
(MAX(金额) FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt--测试结果/*
考核月份 1月 2月 3月 4月
------- ----- ----- ------ -------
福利200210220230
工资100110120130
奖金300310320330
*/--删除环境
Drop table Test
select t1.EmployeeId,t1.Orderid,month(convert(varchar(10),t1.orderdate,121)) as Orderdate
from Orders T1 inner JOIN Customers T2 ON T1.CustomerID=T2.CustomerID)as b
pivot(count(ORDERID)
for ORDERDATE in ( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] ))as c
order by employeeid
起码是愿意分享的牛人最多。
多谢