交叉表求教！

Question

数据如下：
FYear FPeriod 材料名称         数量      单价 成本
2007 1 葡萄糖         101 3.6 363.6
2007 2 葡萄糖         102 4.2 428.4
2007 3 葡萄糖         103 4.1 422.3
2007 4 葡萄糖         104 4.3 447.2
2007 1 收缩膜         101 4.1 414.1
2007 2 收缩膜         102 4.2 428.4
2007 1 丁基胶塞1 101 4.4 444.4
2007 2 丁基胶塞1 101 4.4 444.4
2007 2 丁基胶塞2 101 2.2 222.2
2007 3 丁基胶塞2 111 2.2 244.2
2007 4 丁基胶塞2 101 2.2 222.2
需求结果样式
材料名称 01数量 01单价 01成本 02数量 02单价 02成本 ......     全年数量 全年单价 全年成本
葡萄糖 101 3.6 363.6 102 4.2 428.4    410             4.05             1661.5
收缩膜 .....
丁基胶塞        .....
丁基胶塞        .....谢谢。
我上班才能到这里，请谅解。

Answer 1

declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case FPeriod when '+cast(FPeriod as varchar)+' then 数量 else 0 end) as [0'+cast(FPeriod as varchar)+'数量],max(case FPeriod when '+cast(FPeriod as varchar)+' then 单价 else 0 end) as [0'+cast(FPeriod as varchar)+'单价],sum(case FPeriod when '+cast(FPeriod as varchar)+' then 成本 else 0 end) as [0'+cast(FPeriod as varchar)+'成本]'
from 表
group by FPeriod
order by FPeriodexec('select 材料名称'+@sql+',sum(数量) as 全年数量,sum(成本)/sum(数量) as 全年单价,sum(成本) as 全年成本 from 表 group by 材料名称')

Answer 2

以上写法在FYear，FPeriod，材料名称三字段组合不唯一的时候不对，改下：declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case FPeriod when '+cast(FPeriod as varchar)+' then 数量 else 0 end) as [0'+cast(FPeriod as varchar)+'数量],sum(case FPeriod when '+cast(FPeriod as varchar)+' then 成本 else 0 end)/isnull(nullif(sum(case FPeriod when '+cast(FPeriod as varchar)+' then 数量 else 0 end),0),1) as [0'+cast(FPeriod as varchar)+'单价],sum(case FPeriod when '+cast(FPeriod as varchar)+' then 成本 else 0 end) as [0'+cast(FPeriod as varchar)+'成本]'
from 表
group by FPeriod
order by FPeriodexec('select 材料名称'+@sql+',sum(数量) as 全年数量,sum(成本)/sum(数量) as 全年单价,sum(成本) as 全年成本 from 表 group by 材料名称')

Answer 3

create  table t (FYear INT ,FPeriod INT ,材料名称 Varchar(20), 数量 int,单价 numeric(20,6),成本 numeric(20,6))insert into t(FYear,FPeriod,材料名称,数量,单价,成本)
select 
2007, 1 ,'葡萄糖',  101 ,3.6 ,363.6  union all select 
2007, 2 ,'葡萄糖',  102 ,4.2, 428.4  union all select 
2007, 1 ,'收缩膜',  101 ,4.1 ,414.1  union all select 
2007, 2 ,'收缩膜',   102 ,4.2 ,428.4  union all select 
2007, 1 ,'丁基胶塞1', 101, 4.4 ,444.4  union all select 
2007, 2 ,'丁基胶塞1', 101, 4.4 ,444.4  union all select 
2007, 3 ,'丁基胶塞1', 101, 4.4 ,444.4 declare @sql varchar(8000)
declare @cost numeric(20,6)
select @sql=isnull(@sql,'')+'select 材料名称, '
select @sql=@sql+'max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 数量 else 0 end) as ''数量'+cast(a.FPeriod as varchar(20))+
''',max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 单价 else 0 end) as ''单价'+cast(a.FPeriod as varchar(20))+
''',max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 成本 else 0 end ) as ''成本'+cast(a.FPeriod as varchar(20))+''','  from (select distinct FPeriod from t) as a
select @sql=substring(@sql,1,len(@sql)-1)select @sql=@sql+' from t group by 材料名称  '
print @sqlexec(@sql)

Answer 4

测试表：
create  table t (FYear INT ,FPeriod INT ,材料名称 Varchar(20), 数量 int,单价 numeric(20,6),成本 numeric(20,6))insert into t(FYear,FPeriod,材料名称,数量,单价,成本)
select 2007,1,'葡萄糖', 101,3.6,363.6 
union all select 2007,2,'葡萄糖', 102,4.2,428.4 
union all select 2007,9,'葡萄糖', 102,4.2,428.4 
union all select 2007,10,'葡萄糖', 102,4.2,428.4 
union all select 2007,12,'葡萄糖', 102,4.2,428.4 
union all select 2007,1,'收缩膜', 101,4.1,414.1 
union all select 2007,2,'收缩膜',  102,4.2,428.4 
union all select 2007,1,'丁基胶塞1',101,4.4,444.4 
union all select 2007,2,'丁基胶塞1',101,4.4,444.4 
union all select 2007,3,'丁基胶塞1',101,4.4,444.4

Answer 5

现在是不是楼主想要的效果
create  table t (FYear INT ,FPeriod INT ,材料名称 Varchar(20), 数量 int,单价 numeric(20,6),成本 numeric(20,6))insert into t(FYear,FPeriod,材料名称,数量,单价,成本)
select 
2007,1,'葡萄糖', 101,3.6,363.6 union all select 
2007,2,'葡萄糖', 102,4.2,428.4 union all select 
2007,1,'收缩膜', 101,4.1,414.1 union all select 
2007,2,'收缩膜',  102,4.2,428.4 union all select 
2007,1,'丁基胶塞1',101,4.4,444.4 union all select 
2007,2,'丁基胶塞1',101,4.4,444.4 union all select 
2007,3,'丁基胶塞1',101,4.4,444.4declare @sql varchar(8000)
declare @cost numeric(20,6)
declare @t table(FPeriod int )
declare @i int 
set @i=1
while @i<=12
begin
insert into @t(FPeriod) values(@i)
set @i=@i+1
endselect @sql=isnull(@sql,'')+'select 材料名称, '
select @sql=@sql+'max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 数量 else 0 end) as '''++cast(a.FPeriod as varchar(20))++'数量'',max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 单价 else 0 end) as'''+cast(a.FPeriod as varchar(20))+'单价'',max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 成本 else 0 end ) as '''+cast(a.FPeriod as varchar(20))+'成本'','  from (select  FPeriod from @t ) as a
select @sql=substring(@sql,1,len(@sql)-1)select @sql=@sql+' ,sum(成本) as ''全年成本'' from t group by 材料名称  '
print @sqlexec(@sql)

Answer 6

ivan_ren() :
结果形式基本符合要求
但语句：select @sql=@sql+' ,sum(成本) as ''全年成本'' from t group by 材料名称  '
改变了纪录顺序，我要求纪录顺序不能改变。

Answer 7

--我觉得，既需要返回结果，又要对返回的结果集进行报表制作或者操作什么的。。也可以考虑下这种思路,首先创建一个临时表，然后填充数据
declare @query varchar(200)
declare @monthIndex int 
set @monthIndex = 1
create table  #test
(
  fYear   int,
  Period  varchar(200)
 )
--材料名称01数量01单价01成本02数量02单价02成本 ......     全年数量全年单价全年成本
while (@monthIndex <= 12)
begin
set @query = 'alter table #test add  数量'+ right('0'+cast(@monthIndex as varchar),2) +' float '
exec(@query)
set @query = 'alter table #test add  单价'+ right('0'+cast(@monthIndex as varchar),2) +' float '
exec(@query)
set @query = 'alter table #test add  成本'+ right('0'+cast(@monthIndex as varchar),2) +' float '
exec(@query)
set @monthIndex = @monthIndex + 1
endexec('alter table #test add  全年数量 float ')
exec('alter table #test add  全年单价 float ')
exec('alter table #test add  全年成本 float ')select * from  #test然后可以用楼上几位的语句进行部分更改然后往临时表进行数据填充

Answer 8

借lz地盘问一下：我也有类似的需求，
select @sql=isnull(@sql,'')+'select 材料名称, '
select @sql=@sql+'max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 数量 else 0 end) as '''++cast(a.FPeriod as varchar(20))++'数量'',max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 单价 else 0 end) as'''+cast(a.FPeriod as varchar(20))+'单价'',max(case when FPeriod='+cast(a.FPeriod as varchar(20))+
' then 成本 else 0 end ) as '''+cast(a.FPeriod as varchar(20))+'成本'','  from (select  FPeriod from @t ) as a
select @sql=substring(@sql,1,len(@sql)-1)select @sql=@sql+' ,sum(成本) as ''全年成本'' from t group by 材料名称  '
这个基本满足我的要求，但是这个语句除了在查询分析器中运行，我想在应用程序中调用（delphi），如何使用呢？如何返回结果集到应用程序？
菜菜的问？

Answer 9

这个问题实际应用还是比较多的 一般都是通过报表的设计功能来实现 没用sql语句实现过
值得关注.........

Answer 10

Yang_(扬帆破浪) 的答案改变了数据顺序，我要求按原来的材料名称顺序按月展开，若有好的答案我会马上加分揭帖。

Answer 11

Create Table Table_Pqs
(id Int identity(1,1),
 FYear int default 2007,
 FPeriod int,
 name varchar(40),
 num Int,
 dj int,
 cb int)insert into Table_Pqs(fyear, FPeriod, name, num, dj , cb )
select 2007,1,'葡萄糖',1013,6363,6 union all
select 2007,2,'葡萄糖',1024,2428,4 union all
select 2007,3,'葡萄糖',1034,1422,3 union all
select 2007,4,'葡萄糖',1044,3447,2 union all
select 2007,1,'收缩膜',1014,1414,1 union all
select 2007,2,'收缩膜',1024,2428,4 union all
select 2007,1,'丁基胶塞',11014,4444,4 union all
select 2007,2,'丁基胶塞',11014,4444,4 union all
select 2007,2,'丁基胶塞',21012,2222,2 union all
select 2007,3,'丁基胶塞',21112,2244,2 union all
select 2007,4,'丁基胶塞',21012,2222,2
declare @max int,@i int 
Declare @Sql Varchar(8000)
select @max=FPeriod from Table_Pqs
set @sql='select name as 材料名称'
set @i=1
while @i<=@max
begin
set @sql=@sql + ',sum(Case FPeriod When '+cast(@i as varchar)+' Then num Else 0 End) As ['+right('00'+cast(@i as varchar),2)+'数量]' 
set @sql=@sql + ',sum(Case FPeriod When '+cast(@i as varchar)+' Then dj Else 0 End) As ['+right('00'+cast(@i as varchar),2)+'单价]' 
set @sql=@sql + ',sum(Case FPeriod When '+cast(@i as varchar)+' Then cb Else 0 End) As ['+right('00'+cast(@i as varchar),2)+'成本]' 
set @i=@i+1
end
select @sql = @sql+' from Table_Pqs  Group By name 'exec(@sql)drop table Table_Pqs

Answer 12

要全年的话，直接就不用获取最的数值了，FPeriod是月么？如果是的话
Create Table Table_Pqs
(id Int identity(1,1),
 FYear int default 2007,
 FPeriod int,
 name varchar(40),
 num Int,
 dj int,
 cb int)insert into Table_Pqs(fyear, FPeriod, name, num, dj , cb )
select 2007,1,'葡萄糖',1013,6363,6 union all
select 2007,2,'葡萄糖',1024,2428,4 union all
select 2007,3,'葡萄糖',1034,1422,3 union all
select 2007,4,'葡萄糖',1044,3447,2 union all
select 2007,1,'收缩膜',1014,1414,1 union all
select 2007,2,'收缩膜',1024,2428,4 union all
select 2007,1,'丁基胶塞',11014,4444,4 union all
select 2007,2,'丁基胶塞',11014,4444,4 union all
select 2007,2,'丁基胶塞',21012,2222,2 union all
select 2007,3,'丁基胶塞',21112,2244,2 union all
select 2007,4,'丁基胶塞',21012,2222,2
declare @max int,@i int 
Declare @Sql Varchar(8000)
select @max=12
set @sql='select name as 材料名称'
set @i=1
while @i<=@max
begin
set @sql=@sql + ',sum(Case FPeriod When '+cast(@i as varchar)+' Then num Else 0 End) As ['+right('00'+cast(@i as varchar),2)+'数量]' 
set @sql=@sql + ',sum(Case FPeriod When '+cast(@i as varchar)+' Then dj Else 0 End) As ['+right('00'+cast(@i as varchar),2)+'单价]' 
set @sql=@sql + ',sum(Case FPeriod When '+cast(@i as varchar)+' Then cb Else 0 End) As ['+right('00'+cast(@i as varchar),2)+'成本]' 
set @i=@i+1
end
select @sql = @sql+' from Table_Pqs  Group By name 'exec(@sql)drop table Table_Pqs

Answer 13

同Yang_(扬帆破浪) 的答案一样,改变了数据顺序.

Answer 14

--如果順序真的那麼重要的話，借用下臨時表吧。--創建測試環境
Create Table T(FYear Int, FPeriod Int, 材料名称 Nvarchar(20), 数量 Int, 单价 Numeric(20,6), 成本 Numeric(20,6))
--插入數據
Insert Into T(FYear, FPeriod, 材料名称, 数量, 单价, 成本)
Select 2007,1,N'葡萄糖', 101,3.6,363.6 
Union All Select 2007,2,N'葡萄糖', 102,4.2,428.4 
Union All Select 2007,9,N'葡萄糖', 102,4.2,428.4 
Union All Select 2007,10,N'葡萄糖', 102,4.2,428.4 
Union All Select 2007,12,N'葡萄糖', 102,4.2,428.4 
Union All Select 2007,1,N'收缩膜', 101,4.1,414.1 
Union All Select 2007,2,N'收缩膜',  102,4.2,428.4 
Union All Select 2007,1,N'丁基胶塞1',101,4.4,444.4 
Union All Select 2007,2,N'丁基胶塞1',101,4.4,444.4 
Union All Select 2007,3,N'丁基胶塞1',101,4.4,444.4
GO
--測試
Select ID = Identity(Int, 1, 1), * Into #T From TDeclare @Max Int, @I Int, @S Nvarchar(4000)
Select @Max = 12, @I = 1, @S = N'Select 材料名称'
While @I <= @Max
Begin
Select @S=@S + ', SUM(Case FPeriod When ' + Cast(@I As Varchar) + N' Then 数量 Else 0 End) As [' + Right(100 + @I, 2) + N'数量]' 
+ ', SUM(Case FPeriod When ' + Cast(@I As Varchar) + N' Then 单价 Else 0 End) As [' + Right(100 + @I, 2) + N'单价]' 
+ ', SUM(Case FPeriod When ' + Cast(@I As Varchar) + N' Then 成本 Else 0 End) As [' + Right(100 + @I, 2) + N'成本]' 
Select @I=@I+1
End
Select @S = @S+ N' , SUM(数量) As 全年数量, SUM(单价) As 全年单价, SUM(成本) As 全年成本 From #T Group By FYear, 材料名称 Order By Min(ID) '
EXEC(@S)
Drop Table #T
GO
--刪除測試環境
Drop Table T