表名为table的表内容如下
Year month value
2009 1 1.1
2009 2 1.2
2009 3 1.3
2009 4 1.4
2010 1 2.1
2010 2 2.2
2010 3 2.3
2010 4 2.4
要求查询结果为
year m1 m2 m3 m4
2009 1.11.21.31.4
2010 2.12.22.32.4
请写出SQL 语句。
Year month value
2009 1 1.1
2009 2 1.2
2009 3 1.3
2009 4 1.4
2010 1 2.1
2010 2 2.2
2010 3 2.3
2010 4 2.4
要求查询结果为
year m1 m2 m3 m4
2009 1.11.21.31.4
2010 2.12.22.32.4
请写出SQL 语句。
解决方案 »
- SQL 用事务时默认给表加的什么锁??========马上结贴
- 复杂的汇总结果排序问题,请高手解决!在线等!
- 字段设定为文件的经验
- QQQ:excel 2003 生成的图表,在excel2007中有的颜色丢失了,这个问题如何解决?
- 我发了两个关于sql的问题,现在还是没有解决.没有人能给个正确的方案么?
- sa的cmd 命令用不了。不知是不是
- 急问!!!![dbnetlib][(connectionwrite(wrapperwrite()))]一般网络性错误.请检查网络文档
- 数据库莫名产生“3704”对象被关闭错误!
- ACCESS出错信息如下:不知道是错在哪儿了?(PB调用的)
- 在线等,急!!!关于MSDE的问题!
- 如何设置sql server2005 的 sa账户密码?
- 简单问题,2个表的多个字段合并到一个查询中
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html
set @sql = 'select [Year] '
select @sql = @sql + ' , max(case [month] when ''' + ltrim([month]) + ''' then [value] else 0 end) [' + [month] + ']'
from (select distinct [month] from tb) as a
set @sql = @sql + ' from tb group by [Year]'
print(@sql)
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-27 14:41:35
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Year] int,[month] varchar,[value] numeric(2,1))
insert [tb]
select 2009,1,1.1 union all
select 2009,2,1.2 union all
select 2009,3,1.3 union all
select 2009,4,1.4 union all
select 2010,1,2.1 union all
select 2010,2,2.2 union all
select 2010,3,2.3 union all
select 2010,4,2.4
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [Year] '
select @sql = @sql + ' , max(case [month] when ''' + ltrim([month]) + ''' then value else 0 end) [' + [month] + ']'
from (select distinct [month] from tb) as a
set @sql = @sql + ' from tb group by [Year]'
exec(@sql)
----------------结果----------------------------
/* Year 1 2 3 4
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2009 1.1 1.2 1.3 1.4
2010 2.1 2.2 2.3 2.4(2 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-09-27 14:41:35
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Year] int,[month] int,[value] numeric(2,1))
insert [tb]
select 2009,1,1.1 union all
select 2009,2,1.2 union all
select 2009,3,1.3 union all
select 2009,4,1.4 union all
select 2010,1,2.1 union all
select 2010,2,2.2 union all
select 2010,3,2.3 union all
select 2010,4,2.4
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [Year] '
select @sql = @sql + ' , max(case [month] when ''' + ltrim([month]) + ''' then value else 0 end) [' + 'm'+ltrim([month]) + ']'
from (select distinct [month] from tb) as a
set @sql = @sql + ' from tb group by [Year]'
exec(@sql)
----------------结果----------------------------
/* Year m1 m2 m3 m4
----------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
2009 1.1 1.2 1.3 1.4
2010 2.1 2.2 2.3 2.4(2 行受影响)
*/
if object_id('a') is not null
drop table a
go
create table a(Year char(10) , month varchar(10) , value numeric(2,1))
go
insert into a
select '2009' , '1' , 1.1 union all
select '2009' , '2' , 1.2 union all
select '2009' , '3' , 1.3 union all
select '2009' , '4' , 1.4 union all
select '2010' , '1' , 2.1 union all
select '2010' , '2' , 2.2 union all
select '2010' ,'3' , 2.3 union all
select '2010' , '4' , 2.4
goselect Year , [1] as m1 , [2] as m2 , [3] as m3 , [4] as m4
from a
pivot
(max(value) for month in([1] , [2],[3],[4]))b
===================================
Year m1 m2 m3 m4
-------------------------------------------------
2009 1.1 1.2 1.3 1.4
2010 2.1 2.2 2.3 2.4