有一个表table1结构如下：
销售部门 产品名 期初数量 期末结存
部门1 产品1 100 70
部门1 产品2 80 100
部门1 产品3 120 90
部门2 产品1 50 20
部门2 产品2 60 130
部门3 产品2 190 140
...想转换成如下格式（部门由行转为列，然后分类汇总）：产品名 部门1期初数量 部门1期末数量 部门2期初数量 部门2期末数量 部门3期初数量 部门3期末数量
产品1 100 70 50 20 0 0
小计 100 70 50 20 0 0
产品2 80 100 60 130 190 140
小计 80 100 60 130 190 140
产品3 120 90 0 0 0 0
小计 120 90 0 0 0 0
总计 300 260 110 150 190 140注意：部门很多，是动态数据。
如何实现？多谢各位啦~~~~~~~~~~~~~~~~
销售部门 产品名 期初数量 期末结存
部门1 产品1 100 70
部门1 产品2 80 100
部门1 产品3 120 90
部门2 产品1 50 20
部门2 产品2 60 130
部门3 产品2 190 140
...想转换成如下格式（部门由行转为列，然后分类汇总）：产品名 部门1期初数量 部门1期末数量 部门2期初数量 部门2期末数量 部门3期初数量 部门3期末数量
产品1 100 70 50 20 0 0
小计 100 70 50 20 0 0
产品2 80 100 60 130 190 140
小计 80 100 60 130 190 140
产品3 120 90 0 0 0 0
小计 120 90 0 0 0 0
总计 300 260 110 150 190 140注意：部门很多，是动态数据。
如何实现？多谢各位啦~~~~~~~~~~~~~~~~
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。问题：假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果)：
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
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) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
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 + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名 语文 数学 物理
张三 74 83 93
李四 74 84 94
想变成(得到如下结果)：
姓名 课程 分数
---- ---- ----
李四 语文 74
李四 数学 84
李四 物理 94
张三 语文 74
张三 数学 83
张三 物理 93
--------------
*/create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)
insert into tb values('张三',74,83,93)
insert into tb values('李四',74,84,94)
go--SQL SERVER 2000 静态SQL。
select * from
(
select 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 , 课程 = '物理' , 分数 = 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar(8000)
select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'
from syscolumns
where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列
order by colid asc
exec(@sql + ' order by 姓名 ')--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名 课程 分数
---- ------ ------
李四 语文 74.00
李四 数学 84.00
李四 物理 94.00
李四 平均分 84.00
李四 总分 252.00
张三 语文 74.00
张三 数学 83.00
张三 物理 93.00
张三 平均分 83.33
张三 总分 250.00
------------------
*/select * from
(
select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb
union all
select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb
) t
order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 enddrop table tb
insert xs select '部门1','产品1',100,70
union select '部门1','产品2',80,100
union select '部门1','产品3',120,90
union select '部门2','产品1',50,20
union select '部门2','产品2',60,130
union select '部门3','产品2',190,140
go
--select case when grouping(cp)=1 then '小计' else cp end as 产品,sum(部门1期初) as [部门1期初],sum(部门1期末) as [部门1期末],sum(部门2期初) as [部门2期初],sum(部门2期末) as [部门2期末],sum(部门3期初) as [部门3期初],sum(部门3期末) as [部门3期末] from (select cp,sum(case when bm = '部门1' then qc else 0 end) as [部门1期初],sum(case when bm = '部门1' then qc else 0 end) as [部门1期末],sum(case when bm = '部门2' then qc else 0 end) as [部门2期初],sum(case when bm = '部门2' then qc else 0 end) as [部门2期末],sum(case when bm = '部门3' then qc else 0 end) as [部门3期初],sum(case when bm = '部门3' then qc else 0 end) as [部门3期末] from xs group by cp) b group by cp with rollup order by cp
declare @sql varchar(1000),@sql1 varchar(1000)
select @sql1 = 'select case when grouping(cp)=1 then ''总计'' else cp end as 产品'
select @sql = 'select cp'
select @sql1 = @sql1+','+'sum('+bm+'期初) as ['+bm+'期初],sum('+bm+'期末) as ['+bm+'期末]',
@sql = @sql+',sum(case when bm = '''+bm+''' then qc else 0 end) as ['+bm+'期初]'
+',sum(case when bm = '''+bm+''' then qc else 0 end) as ['+bm+'期末]'
from (select distinct bm from xs) a
select @sql = @sql + ' from xs group by cp'
select @sql1= @sql1+' from ('+@sql+') b group by cp with rollup'
--print @sql1
exec(@sql1)
go
drop table xs
/*
产品 部门1期初 部门1期末 部门2期初 部门2期末 部门3期初 部门3期末
---------- ----------- ----------- ----------- ----------- ----------- -----------
产品1 100 100 50 50 0 0
产品2 80 80 60 60 190 190
产品3 120 120 0 0 0 0
总计 300 300 110 110 190 190
*/
比如增加日期 rq：
create table xs(bm varchar(10),cp varchar(10),rq varchar(10),qc int,qm int)
insert xs select '部门1','产品1','2008-01-01',100,70
union select '部门1','产品2','2008-01-01',80,100
union select '部门1','产品2','2008-01-02',80,100
union select '部门1','产品3','2008-01-01',120,90
union select '部门2','产品1','2008-01-01',50,20
union select '部门2','产品2','2008-01-01',60,130
union select '部门3','产品2','2008-01-01',190,140
godeclare @sql varchar(1000),@sql1 varchar(1000)
select @sql1 = 'select case when grouping(cp)=1 then ''总计'' else cp end as 产品'
+ ',case when grouping(rq)=1 then case when grouping(cp)=1 then ''''
else ''小计'' end else rq end as 日期'
select @sql = 'select cp,rq'
select @sql1 = @sql1+','+'sum('+bm+'期初) as ['+bm+'期初],sum('+bm+'期末) as ['+bm+'期末]',
@sql = @sql+',sum(case when bm = '''+bm+''' then qc else 0 end) as ['+bm+'期初]'
+',sum(case when bm = '''+bm+''' then qc else 0 end) as ['+bm+'期末]'
from (select distinct bm from xs) a
select @sql = @sql + ' from xs group by cp,rq'
select @sql1= @sql1+' from ('+@sql+') b group by cp,rq with rollup'
--print @sql1
exec(@sql1)
go
drop table xs
/*
产品 日期 部门1期初 部门1期末 部门2期初 部门2期末 部门3期初 部门3期末
---------- ---------- ----------- ----------- ----------- ----------- ----------- -----------
产品1 2008-01-01 100 100 50 50 0 0
产品1 小计 100 100 50 50 0 0
产品2 2008-01-01 80 80 60 60 190 190
产品2 2008-01-02 80 80 0 0 0 0
产品2 小计 160 160 60 60 190 190
产品3 2008-01-01 120 120 0 0 0 0
产品3 小计 120 120 0 0 0 0
总计 380 380 110 110 190 190
*/
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (销售部门 varchar(5),产品名 varchar(5),期初数量 int,期末结存 int)
insert into #T
select '部门1','产品1',100,70 union all
select '部门1','产品2',80,100 union all
select '部门1','产品3',120,90 union all
select '部门2','产品1',50,20 union all
select '部门2','产品2',60,130 union all
select '部门3','产品2',190,140--> 静态：不知道小计是如何计算的
select
产品名=case grouping(产品名) when 1 then '总计' else 产品名 end,
部门1期初数量=max(case 销售部门 when '部门1' then 期初数量 else 0 end),
部门1期末数量=max(case 销售部门 when '部门1' then 期末结存 else 0 end),
部门2期初数量=max(case 销售部门 when '部门2' then 期初数量 else 0 end),
部门2期末数量=max(case 销售部门 when '部门2' then 期末结存 else 0 end),
部门3期初数量=max(case 销售部门 when '部门3' then 期初数量 else 0 end),
部门3期末数量=max(case 销售部门 when '部门3' then 期末结存 else 0 end)
from #T
group by 产品名 with rollup--> 动态
declare @SQL nvarchar(4000)
set @SQL = 'select 产品名=case grouping(产品名) when 1 then ''总计'' else 产品名 end'
select @SQL = @SQL + ', ['+销售部门+'期初数量]=max(case 销售部门 when '''+销售部门+''' then 期初数量 else 0 end),['+销售部门+'期末数量]=max(case 销售部门 when '''+销售部门+''' then 期末结存 else 0 end)' from #T group by 销售部门
set @SQL = @SQL + 'from #T group by 产品名 with rollup'
exec (@SQL)/*
产品名 部门1期初数量 部门1期末数量 部门2期初数量 部门2期末数量 部门3期初数量 部门3期末数量
------ ------------- ------------- ------------- ------------- ------------- -----------
产品1 100 70 50 20 0 0
产品2 80 100 60 130 190 140
产品3 120 90 0 0 0 0
总计 120 100 60 130 190 140
*/