现有2表的结构如下:
表:monthData 和表:monthDataDetail
(两表通过MonthDataID关联,MonthDataID 在表monthData中为标识字段,种子为1)
--建表环境
create table monthData(
MonthDataID int,
DepId int ,
myYear int ,
myMonth int
)create table monthDataDetail(
MonthDataID int,
typeId int ,
myCount int
)期望目标:
把2表的数据以如下格式展现(把monthData中的myMonth 数据按照1-12月展开);
DepId myYear typeId 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 谢谢大家了;
表:monthData 和表:monthDataDetail
(两表通过MonthDataID关联,MonthDataID 在表monthData中为标识字段,种子为1)
--建表环境
create table monthData(
MonthDataID int,
DepId int ,
myYear int ,
myMonth int
)create table monthDataDetail(
MonthDataID int,
typeId int ,
myCount int
)期望目标:
把2表的数据以如下格式展现(把monthData中的myMonth 数据按照1-12月展开);
DepId myYear typeId 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月 谢谢大家了;
select 1,1,2007,1
union all select 2,1,2007,2
union all select 3,1,2007,3
union all select 4,2,2007,4
union all select 5,2,2007,5
union all select 6,2,2007,6
union all select 7,4,2007,4
union all select 8,4,2007,5
union all select 9,5,2007,6insert into monthDataDetail
select 1,1,200
union all select 1,2,300
union all select 1,3,400
union all select 2,2,110
union all select 2,3,220
union all select 3,1,230
union all select 3,2,300
union all select 3,3,310
union all select 4,1,320
union all select 4,2,110
union all select 5,1,220
union all select 6,1,230
union all select 7,1,300
union all select 7,2,310
union all select 7,3,320
a.DepId,
a.myYear,
b.typeId,
[01月]=sum(case a.myMonth when 1 then b.myCount else 0 end),
[02月]=sum(case a.myMonth when 2 then b.myCount else 0 end),
[03月]=sum(case a.myMonth when 3 then b.myCount else 0 end),
[04月]=sum(case a.myMonth when 4 then b.myCount else 0 end),
[05月]=sum(case a.myMonth when 5 then b.myCount else 0 end),
[06月]=sum(case a.myMonth when 6 then b.myCount else 0 end),
[07月]=sum(case a.myMonth when 7 then b.myCount else 0 end),
[08月]=sum(case a.myMonth when 8 then b.myCount else 0 end),
[09月]=sum(case a.myMonth when 9 then b.myCount else 0 end),
[10月]=sum(case a.myMonth when 10 then b.myCount else 0 end),
[11月]=sum(case a.myMonth when 11 then b.myCount else 0 end),
[12月]=sum(case a.myMonth when 12 then b.myCount else 0 end)
from
monthData a,monthDataDetail b
where
a.MonthDataID=b.MonthDataID
group by
a.DepId,a.myYear,b.typeId
MonthDataID int,
DepId int ,
myYear int ,
myMonth int
)create table monthDataDetail(
MonthDataID int,
typeId int ,
myCount int
)insert into monthData
select 1,1,2007,1
union all select 2,1,2007,2
union all select 3,1,2007,3
union all select 4,2,2007,4
union all select 5,2,2007,5
union all select 6,2,2007,6
union all select 7,4,2007,4
union all select 8,4,2007,5
union all select 9,5,2007,6insert into monthDataDetail
select 1,1,200
union all select 1,2,300
union all select 1,3,400
union all select 2,2,110
union all select 2,3,220
union all select 3,1,230
union all select 3,2,300
union all select 3,3,310
union all select 4,1,320
union all select 4,2,110
union all select 5,1,220
union all select 6,1,230
union all select 7,1,300
union all select 7,2,310
union all select 7,3,320
select
a.DepId,
a.myYear,
b.typeId,
[01月]=sum(case a.myMonth when 1 then b.myCount else 0 end),
[02月]=sum(case a.myMonth when 2 then b.myCount else 0 end),
[03月]=sum(case a.myMonth when 3 then b.myCount else 0 end),
[04月]=sum(case a.myMonth when 4 then b.myCount else 0 end),
[05月]=sum(case a.myMonth when 5 then b.myCount else 0 end),
[06月]=sum(case a.myMonth when 6 then b.myCount else 0 end),
[07月]=sum(case a.myMonth when 7 then b.myCount else 0 end),
[08月]=sum(case a.myMonth when 8 then b.myCount else 0 end),
[09月]=sum(case a.myMonth when 9 then b.myCount else 0 end),
[10月]=sum(case a.myMonth when 10 then b.myCount else 0 end),
[11月]=sum(case a.myMonth when 11 then b.myCount else 0 end),
[12月]=sum(case a.myMonth when 12 then b.myCount else 0 end)
from
monthData a,monthDataDetail b
where
a.MonthDataID=b.MonthDataID
group by
a.DepId,a.myYear,b.typeId
/*
DepId myYear typeId 01月 02月 03月 04月 05月 06月 07月 08月 09月 10月 11月 12月
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2007 1 200 0 230 0 0 0 0 0 0 0 0 0
1 2007 2 300 110 300 0 0 0 0 0 0 0 0 0
1 2007 3 400 220 310 0 0 0 0 0 0 0 0 0
2 2007 1 0 0 0 320 220 230 0 0 0 0 0 0
2 2007 2 0 0 0 110 0 0 0 0 0 0 0 0
4 2007 1 0 0 0 300 0 0 0 0 0 0 0 0
4 2007 2 0 0 0 310 0 0 0 0 0 0 0 0
4 2007 3 0 0 0 320 0 0 0 0 0 0 0 0
*/
drop table monthData,monthDataDetail
create table monthData(
MonthDataID int,
DepId int ,
myYear int ,
myMonth int
)
create table monthDataDetail(
MonthDataID int,
typeId int ,
myCount int
)
insert into monthData
select 1,1,2007,1
union all select 2,1,2007,2
union all select 3,1,2007,3
union all select 4,2,2007,4
union all select 5,2,2007,5
union all select 6,2,2007,6
union all select 7,4,2007,4
union all select 8,4,2007,5
union all select 9,5,2007,6insert into monthDataDetail
select 1,1,200
union all select 1,2,300
union all select 1,3,400
union all select 2,2,110
union all select 2,3,220
union all select 3,1,230
union all select 3,2,300
union all select 3,3,310
union all select 4,1,320
union all select 4,2,110
union all select 5,1,220
union all select 6,1,230
union all select 7,1,300
union all select 7,2,310
union all select 7,3,320select d.DepId,d.myYear,dd.typeId
,sum(case when d.myMonth = 1 then dd.myCount else 0 end) as [1月]
,sum(case when d.myMonth = 2 then dd.myCount else 0 end) as [2月]
,sum(case when d.myMonth = 3 then dd.myCount else 0 end) as [3月]
,sum(case when d.myMonth = 4 then dd.myCount else 0 end) as [4月]
,sum(case when d.myMonth = 5 then dd.myCount else 0 end) as [5月]
,sum(case when d.myMonth = 6 then dd.myCount else 0 end) as [6月]
,sum(case when d.myMonth = 7 then dd.myCount else 0 end) as [7月]
,sum(case when d.myMonth = 8 then dd.myCount else 0 end) as [8月]
,sum(case when d.myMonth = 9 then dd.myCount else 0 end) as [9月]
,sum(case when d.myMonth = 10 then dd.myCount else 0 end) as [10月]
,sum(case when d.myMonth = 11 then dd.myCount else 0 end) as [11月]
,sum(case when d.myMonth = 12 then dd.myCount else 0 end) as [12月]
from monthData d
join monthDataDetail dd on dd.MonthDataID = d.MonthDataID
group by d.DepId,d.myYear,dd.typeId
go
drop table monthData,monthDataDetail/*
DepId myYear typeId 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2007 1 200 0 230 0 0 0 0 0 0 0 0 0
1 2007 2 300 110 300 0 0 0 0 0 0 0 0 0
1 2007 3 400 220 310 0 0 0 0 0 0 0 0 0
2 2007 1 0 0 0 320 220 230 0 0 0 0 0 0
2 2007 2 0 0 0 110 0 0 0 0 0 0 0 0
4 2007 1 0 0 0 300 0 0 0 0 0 0 0 0
4 2007 2 0 0 0 310 0 0 0 0 0 0 0 0
4 2007 3 0 0 0 320 0 0 0 0 0 0 0 0*/