---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-01-17 20:40:56 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) -- Jun 17 2011 00:57:23 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([DeptId] int,[MonthId] int,[AutoValue] int,[ManualValue] int) insert [huang] select 1,1,100,200 union all select 2,1,100,100 union all select 3,2,200,100 union all select 3,3,200,300 --------------开始查询-------------------------- DECLARE @s NVARCHAR(4000) SET @s = '' SELECT @s = @s + ',' + QUOTENAME([MonthId]) + '=max(case when [MonthId]=' + QUOTENAME([MonthId], '''') + ' then [ManualValue] else 0 end)' FROM [huang] GROUP BY [DeptId],[MonthId] EXEC('select [DeptId]'+@s+' from [huang] group by [DeptId]')
---------------------------------------------------------------- -- Author :DBA_Huangzj(發糞塗牆) -- Date :2013-01-17 20:40:56 -- Version: -- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86) -- Jun 17 2011 00:57:23 -- Copyright (c) Microsoft Corporation -- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([DeptId] int,[MonthId] int,[AutoValue] int,[ManualValue] int) insert [huang] select 1,1,100,200 union all select 2,1,100,100 union all select 3,2,200,100 union all select 3,3,200,300 --------------开始查询-------------------------- DECLARE @s NVARCHAR(4000) SET @s = '' SELECT @s = @s + ',' + QUOTENAME('AutoValue'+CONVERT(VARCHAR(2),MonthId)) + '=max(case when [MonthId]=' + QUOTENAME([MonthId], '''') + ' then [AutoValue] else 0 end)' + ',' + QUOTENAME('ManualValue'+CONVERT(VARCHAR(2),MonthId)) + '=max(case when [MonthId]=' + QUOTENAME([MonthId], '''') + ' then [ManualValue] else 0 end)' FROM [huang] GROUP BY [MonthId] EXEC('select [DeptId]'+@s+' from [huang] group by [DeptId]')
USE test GO -->生成表tbif object_id('tb') is not null drop table tb Go Create table tb([DeptId] smallint,[MonthId] smallint,[AutoValue] smallint,[ManualValue] smallint) Insert into tb Select 1,1,100,200 Union all Select 2,1,100,100 Union all Select 3,2,200,100 Union all Select 3,3,200,300Go DECLARE @sql NVARCHAR(MAX) SELECT @sql=ISNULL(@sql+',','')+'Max(Case when MonthId='+LTRIM(MonthId)+' then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else ''[0][0]'' End) As '+QUOTENAME(MonthId) FROM tb GROUP BY MonthId Exec ('Select DeptId,'+@sql+' From (Select DeptId,MonthId,Sum(AutoValue) As AutoValue,Sum(ManualValue) As ManualValue From tb Group by DeptId,MonthId) As t Group by DeptId') /* Print : Select DeptId ,Max(Case when MonthId=1 then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else '[0][0]' End) As [1] ,Max(Case when MonthId=2 then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else '[0][0]' End) As [2] ,Max(Case when MonthId=3 then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else '[0][0]' End) As [3] From (Select DeptId ,MonthId ,Sum(AutoValue) As AutoValue ,Sum(ManualValue) As ManualValue From tb Group by DeptId,MonthId ) As t Group by DeptIdResult: DeptId 1 2 3 ------ ----------- ----------- ----------- 1 [100][200] [0][0] [0][0] 2 [100][100] [0][0] [0][0] 3 [0][0] [200][100] [200][300]*/
DeptId MonthId AutoValue ManualValue
1 1 100 200
2 1 100 100
3 2 200 100
3 3 200 300
页面显示结果
部门 (月科目ID)1 2 3
1 [100][200] [0] [0] [0][0]
2 [100][100] [0][0] [0][0]
3 [0][0] [200][100] [200][300]
前端一个CELL需要两个text放入自动值和手动值后端这两个值可以单独放一列。但是语句我不会。请教。
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-17 20:40:56
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([DeptId] int,[MonthId] int,[AutoValue] int,[ManualValue] int)
insert [huang]
select 1,1,100,200 union all
select 2,1,100,100 union all
select 3,2,200,100 union all
select 3,3,200,300
--------------开始查询--------------------------
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME([MonthId]) + '=max(case when [MonthId]='
+ QUOTENAME([MonthId], '''') + ' then [ManualValue] else 0 end)'
FROM [huang]
GROUP BY [DeptId],[MonthId]
EXEC('select [DeptId]'+@s+' from [huang] group by [DeptId]')
--select * from [huang]
----------------结果----------------------------
/*
DeptId 1 1 2 3
----------- ----------- ----------- ----------- -----------
1 200 200 0 0
2 100 100 0 0
3 0 0 100 300(3 行受影响)
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-01-17 20:40:56
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (Intel X86)
-- Jun 17 2011 00:57:23
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([DeptId] int,[MonthId] int,[AutoValue] int,[ManualValue] int)
insert [huang]
select 1,1,100,200 union all
select 2,1,100,100 union all
select 3,2,200,100 union all
select 3,3,200,300
--------------开始查询--------------------------
DECLARE @s NVARCHAR(4000)
SET @s = ''
SELECT @s = @s + ',' + QUOTENAME('AutoValue'+CONVERT(VARCHAR(2),MonthId)) + '=max(case when [MonthId]='
+ QUOTENAME([MonthId], '''') + ' then [AutoValue] else 0 end)'
+ ',' + QUOTENAME('ManualValue'+CONVERT(VARCHAR(2),MonthId)) + '=max(case when [MonthId]='
+ QUOTENAME([MonthId], '''') + ' then [ManualValue] else 0 end)'
FROM [huang]
GROUP BY [MonthId]
EXEC('select [DeptId]'+@s+' from [huang] group by [DeptId]')
--select * from [huang]
----------------结果----------------------------
/*
DeptId AutoValue1 ManualValue1 AutoValue2 ManualValue2 AutoValue3 ManualValue3
----------- ----------- ------------ ----------- ------------ ----------- ------------
1 100 200 0 0 0 0
2 100 100 0 0 0 0
3 0 0 200 100 200 300(3 行受影响)*/
GO
-->生成表tbif object_id('tb') is not null
drop table tb
Go
Create table tb([DeptId] smallint,[MonthId] smallint,[AutoValue] smallint,[ManualValue] smallint)
Insert into tb
Select 1,1,100,200
Union all Select 2,1,100,100
Union all Select 3,2,200,100
Union all Select 3,3,200,300Go
DECLARE @sql NVARCHAR(MAX)
SELECT @sql=ISNULL(@sql+',','')+'Max(Case when MonthId='+LTRIM(MonthId)+' then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else ''[0][0]'' End) As '+QUOTENAME(MonthId) FROM tb GROUP BY MonthId
Exec ('Select DeptId,'+@sql+' From (Select DeptId,MonthId,Sum(AutoValue) As AutoValue,Sum(ManualValue) As ManualValue From tb Group by DeptId,MonthId) As t Group by DeptId')
/*
Print :
Select
DeptId
,Max(Case when MonthId=1 then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else '[0][0]' End) As [1]
,Max(Case when MonthId=2 then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else '[0][0]' End) As [2]
,Max(Case when MonthId=3 then Quotename(Isnull(AutoValue,0))+Quotename(Isnull(ManualValue,0)) Else '[0][0]' End) As [3]
From
(Select
DeptId
,MonthId
,Sum(AutoValue) As AutoValue
,Sum(ManualValue) As ManualValue
From tb
Group by DeptId,MonthId
) As t
Group by DeptIdResult:
DeptId 1 2 3
------ ----------- ----------- -----------
1 [100][200] [0][0] [0][0]
2 [100][100] [0][0] [0][0]
3 [0][0] [200][100] [200][300]*/