行转列问题(有点难度)
表Manual_Week_Trend数据
week area1 area2 price
09-03～09-0923233333
09-10～09-16121212211
09-17～09-232333333333
09-24～09-303455523
10-1～10-75688444
10-8～10-1533444444
转为如下
内容09-03～09-0909-10～09-1609-17～09-2309-24～09-3010-1～10-710-8～10-15
area1232 12 233 34 56 334
area2333 1212 333 555 88 44
price33 211 3333 23 444 444
[解决办法]
declare @sql varchar(8000)
set @sql= ' '
select @sql=@sql+',max(case when [week]= '+rtrim([week])+
' then area1 end)as ['+rtrim([week])+'] '
from Manual_Week_Trend
select @sql='select''area1''as [week]'+@sql+ 'from Manual_Week_Trend'
exec(@sql)
[解决办法]
2000:
可用临时表实现
2005可用pivot：
http://blog.csdn.net/roy_88/archive/2007/02/13/1509413.aspx
[解决办法]
use testgocreate table Manual_Week_Trend([week] nvarchar(20),area1 int , area2 int, price int)insert Manual_Week_Trend select '09-03～09-09', 232 , 333, 33 insert Manual_Week_Trend select '09-10～09-16', 12 , 1212 , 211 insert Manual_Week_Trend select '09-17～09-23', 233, 333 , 3333 insert Manual_Week_Trend select '09-24～09-30', 34, 555 , 23 insert Manual_Week_Trend select '10-1～10-7', 56 , 88 ,444 insert Manual_Week_Trend select '10-8～10-15', 334, 44 , 444 goselect [week],area1,ColName='area1' into #t from Manual_Week_Trendunion all select [week],area2,ColName='area2' from Manual_Week_Trendunion all select [week],price,ColName='price' from Manual_Week_Trendgodeclare @s nvarchar(4000)set @s=''select @s=@s+',['+[week]+']=sum(case when [week]='+quotename([week],'''')+' then area1 else 0 end)'from #t group by [week] exec('select ColName'+@s+' from #t group by ColName')ColName 09-03～09-09 09-10～09-16 09-17～09-23 09-24～09-30 10-1～10-7 10-8～10-15 ------- ----------- ----------- ----------- ----------- ----------- ----------- area1 232 12 233 34 56 334area2 333 1212 333 555 88 44price 33 211 3333 23 444 444
[解决办法]
静态方法:use testgocreate table Manual_Week_Trend([week] nvarchar(20),area1 int , area2 int, price int)insert Manual_Week_Trend select '09-03～09-09', 232 , 333, 33 insert Manual_Week_Trend select '09-10～09-16', 12 , 1212 , 211 insert Manual_Week_Trend select '09-17～09-23', 233, 333 , 3333 insert Manual_Week_Trend select '09-24～09-30', 34, 555 , 23 insert Manual_Week_Trend select '10-1～10-7', 56 , 88 ,444 insert Manual_Week_Trend select '10-8～10-15', 334, 44 , 444 goselect ColName, [09-03～09-09]=sum(case when [week]='09-03～09-09' then area1 else 0 end), [09-10～09-16]=sum(case when [week]='09-10～09-16' then area1 else 0 end), [09-17～09-23]=sum(case when [week]='09-17～09-23' then area1 else 0 end), [09-24～09-30]=sum(case when [week]='09-24～09-30' then area1 else 0 end), [10-1～10-7]=sum(case when [week]='10-1～10-7' then area1 else 0 end), [10-8～10-15]=sum(case when [week]='10-8～10-15' then area1 else 0 end) from (select [week],area1,ColName='area1' from Manual_Week_Trend union all select [week],area2,ColName='area2' from Manual_Week_Trend union all select [week],price,ColName='price' from Manual_Week_Trend)tgroup by ColNameColName 09-03～09-09 09-10～09-16 09-17～09-23 09-24～09-30 10-1～10-7 10-8～10-15 ------- ----------- ----------- ----------- ----------- ----------- ----------- area1 232 12 233 34 56 334area2 333 1212 333 555 88 44price 33 211 3333 23 444 444（所影响的行数为 3 行）
[解决办法]
--按benbenkui方法：
create table Manual_Week_Trend
(week varchar(20),area1 int,area2 int,price int)
insert into Manual_Week_Trend select
'09-03～09-09','232','333','33' union all select
'09-10～09-16','12','1212','211' union all select
'09-17～09-23','233','333','3333' union all select
'09-24～09-30','34','555','23' union all select
'10-1～10-7','56','88','444' union all select
'10-8～10-15','334','44','444'
declare @sql varchar(8000)
set @sql='select ''area1'' as week'
select @sql=@sql+',['+[week]+']=max(case when '''+[week]+'''='''+[week]+''' then '+cast(area1 as varchar)+' else 0 end)' from Manual_Week_Trend
select @sql=@sql+'union all select ''area2'' as week'
select @sql=@sql+',['+[week]+']=max(case when '''+[week]+'''='''+[week]+''' then '+cast(area2 as varchar)+' else 0 end)' from Manual_Week_Trend
select @sql=@sql+'union all select ''price'' as week'
select @sql=@sql+',['+[week]+']=max(case when '''+[week]+'''='''+[week]+''' then '+cast(price as varchar)+' else 0 end)' from Manual_Week_Trend
select @sql=@sql+' from Manual_Week_Trend'
exec(@sql)
/*结果
week 09-03~09-09 09-10~09-16 09-17~09-23 09-24~09-30 10-1~10-7 10-8~10-15
----- ----------- ----------- ----------- ----------- ----------- -----------
area1 232 12 233 34 56 334
area2 333 1212 333 555 88 44
price 33 211 3333 23 444 444
*/
[解决办法]
--以下三种方法都可以实现表的90度旋转.
将下表数据：
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
--生成中间数据表
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)
--借助中间表实现行列转换
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@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
----------------------------------------
表1：项目种类 业绩 提成
洗吹类 200 10
外卖 100 5
合计 300 15
转换成:
项目种类 洗吹类 外卖 合计
业绩 200 100 300
提成 10 5 15
if object_id('pubs..tb') is not null
drop table tb
go
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)
go
select 项目种类,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 项目种类 desc
drop table tb
项目种类 洗吹类 外卖 合计
---- ----------- ----------- -----------
业绩 200 100 300
提成 10 5 15
（所影响的行数为 2 行）
数据库中有张表格如下
工资 福利 奖金
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
/*--行列互换的通用存储过程 ： 将指定的表,按指定的字段进行行列互换
--邹建 2004.04--
--使用示例
--测试数据
create table 表(类别 varchar(10),男性 decimal(20,1),女性 decimal(20,1))
insert 表 select '小说',38.0,59.2
union all select '散文',18.9,30.6
union all select '哲学',16.2,10.2
--要求转换结果
/*
性别 小说 散文 哲学
---- ----- ----- -----
男性 38.0 18.9 16.2
女性 59.2 30.6 10.2
*/
--调用存储过程
exec p_zj '表','类别','性别'
--删除测试
drop table 表
*/
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+''''''''
-- ,@s2=@s2+',@'+@i+'=''性别='''''+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<>@fdname
select @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
select * from Test
/*
月份工资福利奖金
1月100200300
2月110210310
3月120220320
4月130230330
*/
exec p_zj 'Test', '月份', '月份'
/*
月份1月2月3月4月
福利200210220230
工资100110120130
奖金300310320330
*/
drop table Test
--drop proc p_zj
静态写法
--测试环境
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
--测试语句
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
[解决办法]
pivot or unpivot