---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-11-25 21:54:27 -- 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.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([部门] varchar(2),[月份] varchar(10),[产量] int) insert [tb] select '01',1,22 union all select '02',2,33 union all select '03',1,22 union all select '01',2,1 --------------开始查询-------------------------- declare @sql varchar(8000) set @sql = 'select [部门] ' select @sql = @sql + ' , max(case [月份] when ''' +[月份] + ''' then [产量] else 0 end) [' + [月份] + ']' from (select distinct [月份]from tb) as a set @sql = @sql + ' from tb group by [部门]' exec(@sql) ----------------结果---------------------------- /* 部门 1 2 ---- ----------- ----------- 01 22 1 02 0 33 03 22 0(3 行受影响) */
select dept as 部门, sum(case when 月份=1 then 产量 else 0 end) as [一月], ... ... from tb group by dept
---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2009-11-25 21:54:27 -- 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.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([部门] varchar(2),[月份] varchar(10),[产量] int) insert [tb] select '01',1,22 union all select '02',2,33 union all select '03',1,22 union all select '01',2,1 --------------开始查询-------------------------- declare @sql varchar(8000) set @sql = 'select [部门] ' select @sql = @sql + ' , max(case [月份] when ''' +[月份] + ''' then [产量] else 0 end) [' + [月份] + '月]' from (select distinct [月份]from tb) as a set @sql = @sql + ' from tb group by [部门]' exec(@sql) ----------------结果---------------------------- /*部门 1月 2月 ---- ----------- ----------- 01 22 1 02 0 33 03 22 0(3 行受影响) */
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-25 21:54:27
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] varchar(2),[月份] varchar(10),[产量] int)
insert [tb]
select '01',1,22 union all
select '02',2,33 union all
select '03',1,22 union all
select '01',2,1
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [部门] '
select @sql = @sql + ' , max(case [月份] when ''' +[月份] + ''' then [产量] else 0 end) [' + [月份] + ']'
from (select distinct [月份]from tb) as a
set @sql = @sql + ' from tb group by [部门]'
exec(@sql)
----------------结果----------------------------
/* 部门 1 2
---- ----------- -----------
01 22 1
02 0 33
03 22 0(3 行受影响)
*/
dept as 部门,
sum(case when 月份=1 then 产量 else 0 end) as [一月],
...
...
from
tb
group by dept
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-25 21:54:27
-- 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.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] varchar(2),[月份] varchar(10),[产量] int)
insert [tb]
select '01',1,22 union all
select '02',2,33 union all
select '03',1,22 union all
select '01',2,1
--------------开始查询--------------------------
declare @sql varchar(8000)
set @sql = 'select [部门] '
select @sql = @sql + ' , max(case [月份] when ''' +[月份] + ''' then [产量] else 0 end) [' + [月份] + '月]'
from (select distinct [月份]from tb) as a
set @sql = @sql + ' from tb group by [部门]'
exec(@sql)
----------------结果----------------------------
/*部门 1月 2月
---- ----------- -----------
01 22 1
02 0 33
03 22 0(3 行受影响)
*/