表 AATX(月份,项目,收入)
测试数据如下:
2006-01,销售,10020
2006-01,成本,8000
2006-01,利润,20202006-02,销售,20020
2006-02,成本,16000
2006-02,利润,40202006-03,销售,14020
2006-03,成本,10000
2006-03,利润,40202006-04,销售,14020
2006-04,成本,11000
2006-04,利润,30202006-05,销售,8000
2006-05,成本,6000
2006-05,利润,2002006-06,销售,6020
2006-06,成本,8000
2006-06,利润,-2020
直到::::
2007-01,销售,110000
2007-01,成本,70000
2007-01,利润,400002007-02,销售,280000
2007-02,成本,200000
2007-02,利润,800002007-03,销售,166020
2007-03,成本,130000
2007-03,利润,36020我想输出成为数据对比表:月份 销售 成本 利润
2006 2007 2006 2007 2006 2007
一月 10020 110020 8000 60000 2020 40000
二月 20020 280000 16000 200000 4020 80000
三月 14020 166020 10000 130000 4020 36020怎么写呢???
测试数据如下:
2006-01,销售,10020
2006-01,成本,8000
2006-01,利润,20202006-02,销售,20020
2006-02,成本,16000
2006-02,利润,40202006-03,销售,14020
2006-03,成本,10000
2006-03,利润,40202006-04,销售,14020
2006-04,成本,11000
2006-04,利润,30202006-05,销售,8000
2006-05,成本,6000
2006-05,利润,2002006-06,销售,6020
2006-06,成本,8000
2006-06,利润,-2020
直到::::
2007-01,销售,110000
2007-01,成本,70000
2007-01,利润,400002007-02,销售,280000
2007-02,成本,200000
2007-02,利润,800002007-03,销售,166020
2007-03,成本,130000
2007-03,利润,36020我想输出成为数据对比表:月份 销售 成本 利润
2006 2007 2006 2007 2006 2007
一月 10020 110020 8000 60000 2020 40000
二月 20020 280000 16000 200000 4020 80000
三月 14020 166020 10000 130000 4020 36020怎么写呢???
Select
Right(月份, 2) As 月份,
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'销售' Then 收入 Else 0 End) As [2006销售],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'销售' Then 收入 Else 0 End) As [2007销售],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'成本' Then 收入 Else 0 End) As [2006成本],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'成本' Then 收入 Else 0 End) As [2007成本],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'利润' Then 收入 Else 0 End) As [2006销售],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'利润' Then 收入 Else 0 End) As [2007利润]
From
AATX
Group By
Right(月份, 2)
Order By
月份
(月份 Char(7),
项目 Nvarchar(20),
收入 Int)
Insert AATX Select '2006-01',N'销售',10020
Union All Select '2006-01',N'成本',8000
Union All Select '2006-01',N'利润',2020
Union All Select '2006-02',N'销售',20020
Union All Select '2006-02',N'成本',16000
Union All Select '2006-02',N'利润',4020
Union All Select '2006-03',N'销售',14020
Union All Select '2006-03',N'成本',10000
Union All Select '2006-03',N'利润',4020
Union All Select '2006-04',N'销售',14020
Union All Select '2006-04',N'成本',11000
Union All Select '2006-04',N'利润',3020
Union All Select '2006-05',N'销售',8000
Union All Select '2006-05',N'成本',6000
Union All Select '2006-05',N'利润',200
Union All Select '2006-06',N'销售',6020
Union All Select '2006-06',N'成本',8000
Union All Select '2006-06',N'利润',-2020
Union All Select '2007-01',N'销售',110000
Union All Select '2007-01',N'成本',70000
Union All Select '2007-01',N'利润',40000
Union All Select '2007-02',N'销售',280000
Union All Select '2007-02',N'成本',200000
Union All Select '2007-02',N'利润',80000
Union All Select '2007-03',N'销售',166020
Union All Select '2007-03',N'成本',130000
Union All Select '2007-03',N'利润',36020
GO
--如果是年,项目是固定的
Select
Right(月份, 2) As 月份,
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'销售' Then 收入 Else 0 End) As [2006销售],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'销售' Then 收入 Else 0 End) As [2007销售],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'成本' Then 收入 Else 0 End) As [2006成本],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'成本' Then 收入 Else 0 End) As [2007成本],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'利润' Then 收入 Else 0 End) As [2006利润],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'利润' Then 收入 Else 0 End) As [2007利润]
From
AATX
Group By
Right(月份, 2)
Order By
月份----如果是年 ,项目是固定的
Declare @S Nvarchar(4000)
Select @S = N'Select Right(月份, 2) As 月份'
Select @S = @S + N' , SUM(Case When Left(月份, 4) = ''' + 月份 + N''' And 项目 = N''' + 项目 + N''' Then 收入 Else 0 End) As [' + 月份 + 项目 + ']'
From (Select A.*, B. *From (Select Distinct Left(月份, 4) As 月份 From AATX) A Cross Join (Select Distinct 项目 From AATX) B ) C Order By 月份, 项目
Select @S = @S + N'From AATX Group By Right(月份, 2) Order By 月份'
EXEC(@S)
GO
Drop Table AATX
--Result
/*
月份 2006销售 2007销售 2006成本 2007成本 2006利润 2007利润
01 10020 110000 8000 70000 2020 40000
02 20020 280000 16000 200000 4020 80000
03 14020 166020 10000 130000 4020 36020
04 14020 0 11000 0 3020 0
05 8000 0 6000 0 200 0
06 6020 0 8000 0 -2020 0
*/
(月份 Char(7),
项目 Nvarchar(20),
收入 Int)
Insert AATX Select '2006-01',N'销售',10020
Union All Select '2006-01',N'成本',8000
Union All Select '2006-01',N'利润',2020
Union All Select '2006-02',N'销售',20020
Union All Select '2006-02',N'成本',16000
Union All Select '2006-02',N'利润',4020
Union All Select '2006-03',N'销售',14020
Union All Select '2006-03',N'成本',10000
Union All Select '2006-03',N'利润',4020
Union All Select '2006-04',N'销售',14020
Union All Select '2006-04',N'成本',11000
Union All Select '2006-04',N'利润',3020
Union All Select '2006-05',N'销售',8000
Union All Select '2006-05',N'成本',6000
Union All Select '2006-05',N'利润',200
Union All Select '2006-06',N'销售',6020
Union All Select '2006-06',N'成本',8000
Union All Select '2006-06',N'利润',-2020
Union All Select '2007-01',N'销售',110000
Union All Select '2007-01',N'成本',70000
Union All Select '2007-01',N'利润',40000
Union All Select '2007-02',N'销售',280000
Union All Select '2007-02',N'成本',200000
Union All Select '2007-02',N'利润',80000
Union All Select '2007-03',N'销售',166020
Union All Select '2007-03',N'成本',130000
Union All Select '2007-03',N'利润',36020
GO
--如果是年,项目是固定的
Select
Right(月份, 2) As 月份,
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'销售' Then 收入 Else 0 End) As [2006销售],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'销售' Then 收入 Else 0 End) As [2007销售],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'成本' Then 收入 Else 0 End) As [2006成本],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'成本' Then 收入 Else 0 End) As [2007成本],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'利润' Then 收入 Else 0 End) As [2006利润],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'利润' Then 收入 Else 0 End) As [2007利润]
From
AATX
Group By
Right(月份, 2)
Order By
月份----如果是年 ,项目不是固定的
Declare @S Nvarchar(4000)
Select @S = N'Select Right(月份, 2) As 月份'
Select @S = @S + N' , SUM(Case When Left(月份, 4) = ''' + 月份 + N''' And 项目 = N''' + 项目 + N''' Then 收入 Else 0 End) As [' + 月份 + 项目 + ']'
From (Select A.*, B. *From (Select Distinct Left(月份, 4) As 月份 From AATX) A Cross Join (Select Distinct 项目 From AATX) B ) C Order By 月份, 项目
Select @S = @S + N'From AATX Group By Right(月份, 2) Order By 月份'
EXEC(@S)
GO
Drop Table AATX
--Result
/*
月份 2006销售 2007销售 2006成本 2007成本 2006利润 2007利润
01 10020 110000 8000 70000 2020 40000
02 20020 280000 16000 200000 4020 80000
03 14020 166020 10000 130000 4020 36020
04 14020 0 11000 0 3020 0
05 8000 0 6000 0 200 0
06 6020 0 8000 0 -2020 0
*/
Select
Right(月份, 2) As 月份,
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'销售' Then 收入 Else 0 End) As [2006销售],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'销售' Then 收入 Else 0 End) As [2007销售],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'成本' Then 收入 Else 0 End) As [2006成本],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'成本' Then 收入 Else 0 End) As [2007成本],
SUM(Case When Left(月份, 4) = '2006' And 项目 = N'利润' Then 收入 Else 0 End) As [2006利润],
SUM(Case When Left(月份, 4) = '2007' And 项目 = N'利润' Then 收入 Else 0 End) As [2007利润]
From
AATX
Group By
Right(月份, 2)
Order By
月份--如果年 ,项目不是固定的
Declare @S Nvarchar(4000)
Select @S = N'Select Right(月份, 2) As 月份'
Select @S = @S + N' , SUM(Case When Left(月份, 4) = ''' + 年份 + N''' And 项目 = N''' + 项目 + N''' Then 收入 Else 0 End) As [' + 年份 + 项目 + ']'
From (Select A.*, B. *From (Select Distinct Left(月份, 4) As 年份 From AATX) A Cross Join (Select Distinct 项目 From AATX) B ) C Order By 项目, 年份
Select @S = @S + N'From AATX Group By Right(月份, 2) Order By 月份'
EXEC(@S)
子陌哥哥以前帮我写的,感觉我们是一样的需求