Table:
Date Budget SO Invoice
2012-06-01 100 50 30
2012-06-02 120 40 60
2012-06-03 90 60 40
2012-06-04 110 80 60
........我想要的结果是:
date 2012-06-01 2012-06-02 2012-06-03 2012-06-04 ......
budget 100 120 90 110
SO 50 40 60 80
Invoice 30 60 40 60
Date Budget SO Invoice
2012-06-01 100 50 30
2012-06-02 120 40 60
2012-06-03 90 60 40
2012-06-04 110 80 60
........我想要的结果是:
date 2012-06-01 2012-06-02 2012-06-03 2012-06-04 ......
budget 100 120 90 110
SO 50 40 60 80
Invoice 30 60 40 60
解决方案 »
- 如何有效避免表被锁死
- 还是刚才哪个问题 varchar 比较大小 where 语句咋写
- 如何刪除列
- 如何提高这句SQL语句的效率,子查询
- 在左边的数据库目录里,昨天还显示有数据库的名字,可今天开机以后没有数据库的名字显示了,出现了“无项目”三个字。是什么原因呢?
- sqlserver有没有像oracle中的rownum东西,如果没有如何解决?
- MSSQL 2000与 MSSLQ 2005到底那个更优胜???
- 如何更改服务器名?
- 数据库的还原问题,急,请高手帮忙
- 请问SYBASE中CONVERT()函数的第3个参数的含义?
- server2005链接服务器访问oracle出错
- 数据库中表的属性如何设置多选项?
--行列互转
--摘自中国风博客,引用请标明内容来源
--1、行换列
if object_id('Class') is not null
drop table Class
Go
Create table Class(
[Student] nvarchar(2),
[Course] nvarchar(2),
[Score] int)
Insert Class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85
Go--2000方法:
--动态:
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='
+quotename([Course],'''')+' then [Score] else 0 end)'
from
Class group by[Course]
--select @s
exec('select [Student]'+@s+' from Class group by [Student]')
--生成静态:
select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end)
from
Class
group by [Student]
GO
--动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
select @s
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')--生成静态:
select *
from
Class
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b--生成格式:
/*
Student 数学 物理 英语 语文
------- ----------- ----------- ----------- -----------
李四 77 85 65 65
张三 87 90 82 78(2 行受影响)
*/go
--加上总成绩(学科平均分)--2000方法:
--动态:declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+'then [Score] else 0 end)'
from Class group by[Course]
exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多一列(学科平均分用avg([Score]))生成动态:select
[Student],
[数学]=max(case when [Course]='数学' then [Score] else 0 end),
[物理]=max(case when [Course]='物理' then [Score] else 0 end),
[英语]=max(case when [Course]='英语' then [Score] else 0 end),
[语文]=max(case when [Course]='语文' then [Score] else 0 end),
[总成绩]=([Score]) --加多一列(学科平均分用avg([Score]))
from
Class
group by [Student]go--2005方法:动态:declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
--isnull(@s+',','') 去掉字符串@s中第一个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')--生成静态:select
[Student],[数学],[物理],[英语],[语文],[总成绩]
from
(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时用avg([Score])
pivot
(max([Score]) for [Course] in([数学],[物理],[英语],[语文]))b 生成格式:/*
Student 数学 物理 英语 语文 总成绩
------- ----------- ----------- ----------- ----------- -----------
李四 77 85 65 65 292
张三 87 90 82 78 337(2 行受影响)
*/go--2、列转行
if not object_id('Class') is null
drop table Class
Go
Create table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'张三',87,90,82,78
Go--2000:动态:declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第一个union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一个排序生成静态:
select *
from (select [Student],[Course]='数学',[Score]=[数学] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英语',[Score]=[英语] from Class union all
select [Student],[Course]='语文',[Score]=[语文] from Class)t
order by [Student],[Course]go
--2005:动态:declare @s nvarchar(4000)
select @s=isnull(@s+',','')+quotename(Name)
from syscolumns where ID=object_id('Class') and Name not in('Student')
order by Colid
exec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')go
select
Student,[Course],[Score]
from
Class
unpivot
([Score] for [Course] in([数学],[物理],[英语],[语文]))b
生成格式:
/*
Student Course Score
------- ------- -----------
李四 数学 77
李四 物理 85
李四 英语 65
李四 语文 65
张三 数学 87
张三 物理 90
张三 英语 82
张三 语文 78
*/
--用动态的就好了
create table mic
(cDate date, Budget int, SO int, Invoice int)insert into mic
select '2012-06-01', 100, 50, 30 union all
select '2012-06-02', 120, 40, 60 union all
select '2012-06-03', 90, 60, 40 union all
select '2012-06-04', 110, 80, 60
select c 'date',[2012-06-01],[2012-06-02],[2012-06-03],[2012-06-04]
from
(select cDate,c,v
from mic a
unpivot(v for c in([Budget],[SO],[Invoice])) u) b
pivot(max(v) for cDate
in([2012-06-01],[2012-06-02],[2012-06-03],[2012-06-04])) p/*
date 2012-06-01 2012-06-02 2012-06-03 2012-06-04
---------- ----------- ----------- ----------- -----------
Budget 100 120 90 110
Invoice 30 60 40 60
SO 50 40 60 80(3 row(s) affected)
*/