动态列+Pivot交叉表,结果集出错

Question

动态列+Pivot交叉表,结果集出错，RemainAmountFor列不见了为什么交叉表，在套几个动态的字段交叉出来的数据就乱了思路：1.先根据@ListID,@ListName加载动态列；2.再根据AgeGroupName列里面的内容，交叉成新的列RemainAmountFor,PVData类型为数字型
Declare @m Varchar(1000)
Set @m=''
Select @m=@m+',['+AgeGroupName+']' From #TmpLast Group By AgeGroupName
SET @m=STUFF(@m,1,1,'')
Declare @mstr Varchar(5000)
Set @mstr=''
Set @mstr=@mstr+'
Select a.AccountID,a.AccountCode,a.AccountName,a.CheckItemListID,a.DebitOrCredit
,a.RemainAmountFor'
Set @mstr=@mstr+''+@ListID+''       --@ListID,@ListName为动态的n列
Set @mstr=@mstr+''+@ListName+''
Set @mstr=@mstr+',a.PVData,a.AgeGroupName'
Set @mstr=@mstr+'
Into #TmpLast02
From #TmpLast a
Left Join FIGL_Bas_CheckItemList_H list On a.CheckItemListID=list.CheckItemListID
Where list.CompanyID='''+Cast(@CompanyID As Varchar(40))+'''

Select * From #TmpLast02
' Set @mstr=@mstr+'
Select AccountID,AccountCode,AccountName,CheckItemListID,DebitOrCredit
,RemainAmountFor
'
Set @ListID=Replace(@ListID,',list.I@','I@')
Set @ListName=Replace(@ListName,'list.N@','N@') Set @mstr=@mstr+''+@ListID+''
Set @mstr=@mstr+''+@ListName+''
Set @mstr=@mstr+','+@m+''
Set @mstr=@mstr+'
From #TmpLast02 
Pivot
(
Max(PVData)
For AgeGroupName In ('+@m+')
) As PT
'
Set @mstr=@mstr+' Drop Table #TmpLast02
'
Print(@mstr)
Exec(@mstr)
请各位留步

Answer 1

/*★★★★★soft_wsx  2009-08-09 14:08:28.733★★★★★*/
--原始数据如下
if object_id('tb') is not null drop table tb
  create table tb(quyu nvarchar(20), yf nvarchar(7),
                  jhje decimal(14,2), xsje decimal(14,2), xscb decimal(14,2),
                  kcje decimal(14,2), ml decimal(14,2),mll decimal(14,2))
insert tb
select '安徽', '2009-01', 100.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '安徽', '2009-02', 200.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '安徽', '2009-03', 300.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '安徽2', '2009-04', 400.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '安徽2', '2009-05', 500.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '安徽2', '2009-06', 600.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '北京', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '北京', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '北京', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '北京2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '北京2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '北京2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '云南', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '云南', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '云南', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '云南2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '云南2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '云南2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '四川', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '四川', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '四川', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '四川2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '四川2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '四川2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '上海', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '上海', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '上海', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '上海2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '上海2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '上海2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '深圳', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '深圳', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '深圳', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '深圳2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '深圳2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '深圳2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '成都', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '成都', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '成都', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '成都2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '成都2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '成都2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '湛江', '2009-01', 12.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '湛江', '2009-02', 13.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '湛江', '2009-03', 14.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '湛江2', '2009-04', 15.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '湛江2', '2009-05', 16.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '湛江2', '2009-06', 17.00, 0.00, 0.00, 0.00, 0.00, 0.00union all select '广州', '2009-01', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '广州', '2009-02', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '广州', '2009-03', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '广州2', '2009-04', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '广州2', '2009-05', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00
union all select '广州2', '2009-06', 0.00, 0.00, 0.00, 0.00, 0.00, 0.00/*
得到如下结果
  月份      安微_jhje  安微_xsje 安微_xscb 安微_kcje 安微_ml 安微_mll   北京_jhje  北京_xsje 北京_xscb 北京_kcje 北京_ml 北京_mll ..........
2009-01   0.00     0.00 0.00     0.00 0.00 0.00 0.00     0.00 0.00     0.00 0.00 0.00........
2009-02  0.00     0.00 0.00     0.00 0.00 0.00 0.00     0.00 0.00     0.00 0.00 0.00........
2009-03     0.00     0.00 0.00     0.00 0.00 0.00 0.00     0.00 0.00     0.00 0.00 0.00........
2009-04  0.00     0.00 0.00 0.00 0.00 0.00 0.00     0.00 0.00     0.00 0.00 0.00........
2009-05     0.00     0.00 0.00 0.00 0.00 0.00 0.00     0.00 0.00     0.00 0.00 0.00........
2009-06     0.00     0.00 0.00 0.00 0.00 0.00 0.00     0.00 0.00     0.00 0.00 0.00........
................................................................................................................................................
 总计       500       ..........................................................................................................................
 
 */create proc dbo.hljcbb  -- exec dbo.hljcbb
as
declare @sql_head                           nvarchar(4000),
        @sql_foot                           nvarchar(4000),
        @sql_body                           nvarchar(4000),
        @sql_variable_definition            nvarchar(4000),
        @sql_variable_init                  nvarchar(4000),
        @sql_variable_set                   nvarchar(4000),
        @groups                             varchar(100)-->>>>>>1、确定需要定义的变量个数>>>>>>>>>drop table #1
select
   id=identity(int,0,1),
   gs=0,
   fieldvalue=CAST(
                   N','+quotename(quyu+'_jhje')
                   +N'=max(case when quyu='+quotename(quyu,N'''')
                   +N' then jhje else 0 end)'+CHAR(13)+
                   +N','+quotename(quyu+'_xsje')
                   +N'=max(case when quyu='+quotename(quyu,N'''')
                   +N' then xsje else 0 end)'+CHAR(13)+
                   +N','+quotename(quyu+'_xscb')
                   +N'=max(case when quyu='+quotename(quyu,N'''')
                   +N' then xscb else 0 end)'+CHAR(13)+
                   +N','+quotename(quyu+'_kcje')
                   +N'=max(case when quyu='+quotename(quyu,N'''')
                   +N' then kcje else 0 end)'+CHAR(13)+
                   +N','+quotename(quyu+'_ml')
                   +N'=max(case when quyu='+quotename(quyu,N'''')
                   +N' then ml else 0 end)'+CHAR(13)+
                   +N','+quotename(quyu+'_mll')
                   +N'=max(case when quyu='+quotename(quyu,N'''')
                   +N' then mll else 0 end)'
                   as Nvarchar(4000))
  into #1 from tb
  group by quyu
  
 -->>>>>2、分临时时表，通过G字段的值决定当前的处理代码应保存在那个变量中
 update A 
   set @groups=id/l,gs=@groups
   from #1 a 
     cross join(select l=3800/max(LEN(FIELDVALUE)) from #1)b-->>>>>>3.生成数据处理语句--select * from #1
select @sql_head=N''''+REPLACE('select yf ',N'''',N'''''')+N'''',
       @sql_foot=N''''+REPLACE(' from tb group by yf',N'''',N'''''')+N'''',
       @sql_variable_definition=N'',
       @sql_variable_init=N'',
       @sql_variable_set=N'',
       @sql_body=N''
while @groups>=0
  select 
    @sql_variable_definition=N',@'+@groups+N' nvarchar(4000)'+@sql_variable_definition,
    @sql_variable_init=N',@'+@groups+N'=N''''',
    @sql_variable_set=N',@'+@groups+N'=case 
                                         when gs='+@groups+N' then @'+@groups+N'+fieldvalue
                                         else @'+@groups+N'
                                       end'+@sql_variable_set,
    @sql_body=N'+@'+@groups+@sql_body,
    @groups=@groups-1--去掉各变量中多余的前导符号
select 
    @sql_variable_definition=STUFF(@sql_variable_definition,1,1,N''), --stuff删除指定位置开始，指定数目的字符串，并在指定位置插入字符
    @sql_variable_init=STUFF(@sql_variable_init,1,1,N''),
    @sql_variable_set=STUFF(@sql_variable_set,1,1,N''),
    @sql_body=STUFF(@sql_body,1,1,N'')
print @sql_head
print @sql_foot
print @sql_variable_definition
print @sql_variable_set
print @sql_variable_init
print @sql_body
--执行
exec(N'declare '+@sql_variable_definition+
     N'select '+@sql_variable_init+
     N'select '+@sql_variable_set+
     N' from #1
exec(N'+@sql_head+N'
        +'+@sql_body+N'
        +'+@sql_foot+N')')
go
/*
yf 安徽_jhje 安徽_xsje 安徽_xscb 安徽_kcje 安徽_ml 安徽_mll 安徽2_jhje 安徽2_xsje 安徽2_xscb 安徽2_kcje 安徽2_ml 安徽2_mll
2009-01 100.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
2009-02 200.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
2009-03 300.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
2009-04 0.00 0.00 0.00 0.00 0.00 0.00 400.00 0.00 0.00 0.00 0.00 0.00
2009-05 0.00 0.00 0.00 0.00 0.00 0.00 500.00 0.00 0.00 0.00 0.00 0.00
2009-06 0.00 0.00 0.00 0.00 0.00 0.00 600.00 0.00 0.00 0.00 0.00 0.00
*/换种方法！存储过程+动态

Answer 2

楼主能否把你最后的SQL语句PRINT出来，看着难受。

Answer 3

不好意思，自己写错了，前面掉了一个,号
奇怪的是语法也没错，也查询出结果来
修正后的sql为print(@mstr) 打印出来的结果Select a.AccountID,a.AccountCode,a.AccountName,a.CheckItemListID,a.DebitOrCredit
,a.RemainAmountFor,list.I@000000010000000001,list.N@000000010000000001,a.PVData,a.AgeGroupName
Into #TmpLast02
From #TmpLast a
Left Join FIGL_Bas_CheckItemList_H list On a.CheckItemListID=list.CheckItemListID
Where list.CompanyID='00000001'

Select * From #TmpLast02

Select AccountID,AccountCode,AccountName,CheckItemListID,DebitOrCredit
,RemainAmountFor
,I@000000010000000001,N@000000010000000001,[已到期(1--10天)],[已到期(11--20天)]
From #TmpLast02 
Pivot
(
Max(PVData)
For AgeGroupName In ([已到期(1--10天)],[已到期(11--20天)])
) As PT
Drop Table #TmpLast02

Answer 4

        Set @ListID=Replace(@ListID,',list.I@','I@') --结果是这里吧，号漏了
        Set @ListName=Replace(@ListName,'list.N@','N@')还是谢谢各位大家的交叉表都是怎么做的？请给个意见，谢谢

Answer 5

PIVOT还是有它自己的缺陷的 建议还是使用case 和union的老方法来解决

Answer 6

好多时候，把字符串print出来了，一看便知道了
还是小心为妙

Answer 7

如果这样！用我的（其实是老大的，学他的，呵呵）方法就可以了！PIVOT很少用！嘻嘻

Answer 8

非得要PVIOT吗？case when 不行吗？

Answer 9

谢谢乔峰大哥，已经查出问题来了刚开始用Pivot不熟啊