关于交叉表的问题

Question

create table tb(id int,UserID INT,ZPJob VARCHAR(50))INSERT TB
SELECT  1   ,   1     ,       '.Net'
UNION ALL SELECT 2   ,   1   ,          'SQL'
UNION ALL SELECT 3    ,   2 ,           'DelPhi'
UNION ALL SELECT 4     ,  1,            'VB'GO--查詢處理
declare @s nvarchar(4000),@i int
select top 1 @s='',@i=count(distinct ZPJob)
from tb group by UserID
order by count(distinct ZPJob) desc
while @i>0
select @s=',ZPJob'+CAST(@i AS VARCHAR(20))
+'=isnull(max(case i when '+rtrim(@i)
+' then ZPJob end),'''')'+@s,
@i=@i-1
print @s
exec('select UserID'+@s+' from(
select UserID,ZPJob,i=(select count(distinct ZPJob) from tb where UserID=a.UserID and ZPJob<=a.ZPJob)
from tb a
)aa group by UserID')
go--刪除測試
drop table tb

Answer 1

create table tb(id int,UserID INT,ZPJob VARCHAR(50))INSERT TB
SELECT  1   ,   1     ,       '.Net'
UNION ALL SELECT 2   ,   1   ,          'SQL'
UNION ALL SELECT 3    ,   2 ,           'DelPhi'
UNION ALL SELECT 4     ,  1,            'VB'declare @s varchar(8000),@i int
set @s='select userid'
set @i=1
select @s=@s+',[zpjob'+cast(@i as varchar)+']=max(case zpjob when '''+zpjob+''' then zpjob else '''' end )'
,@i=@i+1 from tb group by zpjob
set @s=@s+' from tb group by userid'
exec(@s)
drop table tb

Answer 2

declare @s varchar(2000)
declare @i int
set @i=0
set @s='select userid'
select @i=@i+1,@s=@s+',[zpjob'+convert(varchar(10),@i)+']=max(case when  zpjob='''+zpjob+''' then '''+zpjob+''' else null end)'
from # A
group by zpjob
set @s=@s+' from #  group by userid'
exec(@s)

Answer 3

TO:xiaomeixiang(小山羊：) 咩…咩……   and  wgsasd311(自强不息) 
你们的语句可以实现我以上语句的要求，但是有一个问题就是
如果我的表的语句有很多呢，也就是说我的用户USERID 从1－10000呢，那这个
语句该怎么写呢。

Answer 4

如果我的表的语句有很多呢，也就是说我的用户USERID 从1－10000呢，那这个
语句该怎么写呢。==========>上面的语句，无论表的记录有多少（即无论多少用户）都是通用的。

Answer 5

INSERT TB
SELECT 1   ,   1     ,       '.Net'
UNION ALL SELECT 2   ,   1   ,          'SQL'
UNION ALL SELECT 3    ,   2 ,           'DelPhi'
UNION ALL SELECT 4     ,  1,            'VB'但是上面录入数据的userid 你不是写定了么（1，1，2，1），如果用户有3,4,5,6
是不是要接着使用UNION ALL SELECT 写下去啊

Answer 6

但是上面录入数据的userid 你不是写定了么（1，1，2，1），如果用户有3,4,5,6
是不是要接着使用UNION ALL SELECT 写下去啊
=========>我只是为了让你看到结果，随便录了几条数据，此数据你应该是以经存在表里了，不需要录的。

Answer 7

在关系数据库中，经常为了使数据库更符合第三范式，同时也是从数据存取速度和空间使用率来考虑。在数据结构设计的时候，会尽量减少记录长度。而且有时候为了使程序尽量通用化和产品化，通过用户个性化设置数据来满足信息的多样化需求。但是体现给最终用户是要求能直观的报表形式，所以，就要用到交叉表的技术，笔者这里有一段代码，非常有用，希望能给大家带来方便。CREATE   PROCEDURE CrossTable --交叉表生成器
--(Select Distinct SuppCode As SCode,MCode,PurQtyRate As Rate
--        From MtSPM A Inner Join MtSPD B On A.ID=B.MtSuppPriceM_ID
--                     Inner Join Suppliers C On A.Supp_ID=C.ID
--        Where A.Active_KID=120 And AduitPass=1) D
@vSourceTAB As Varchar(2000), --数据来源表,可以为表,视图,或者SQL语句(要用括号以及别名:如上注释段)
@vGroupbyField As Varchar(50), --被selct Group By 要显示出来的,可以多个字段(记录可以有空值)
@vTransFormCol As Varchar(50), --交叉表中的合计等函数计算值的字段
@vFunction As Varchar(50)=' Sum', --默认值,交叉表中的函数,也可以是' 2*Sum'的计算公式
@vPivotCol As Varchar(50), --要转换成列的字段,唯一列,可以是表达式'Field1+Field2'(记录可以有空值)
@vStrWhere As Varchar(500) =Null --Where 约束条件,可以为空
AS
--重要提示:@StrSql的Largest size allowed Is 8000,因此尽量将少的字段内容转换为列
Declare @StrSql As Varchar(8000) --//总的SQL语句
Declare @StrSum As Varchar(3000) --//列合计
Declare @pCols As Varchar(100)
Declare @StrWhere As Varchar(500)
Execute('Declare CursorCross Cursor For  
          Select Distinct ' + @vPivotCol + ' From ' +@vSourceTAB +' Order By ' + @vPivotCol + ' For Read only ')
Begin
  Set Nocount On
  Set @StrSql =''
  Set @StrSum=''
  Set @pCols=''
  IF Rtrim(Ltrim(IsNull(@vStrWhere,''))) <> ''
  Begin
    Set @StrWhere=' Where ' + @vStrWhere + ' '
  End
Else
  Set @StrWhere=''
  Open CursorCross
  While (0=0)
  Begin
    Fetch Next From CursorCross Into @pCols
    IF (@@Fetch_Status<>0) Break
    IF @pCols Is  Null --//不为空值,
    Set @pCols='Null'
    --为了防止新创建的列的标题名称,与@vGroupbyField中的字段重名,
    --新创建的列的标题名称都增加前缀[@vGroupbyField.新创建的列的标题名称]
--因Sql长度限制Max=8000,由源数据控制字段值不能为Null,因此这里不再检验值是否为Null
    Set @StrSql=@StrSql +',' + @vFunction +
        '(Case '+@vPivotCol+' When ' + @pCols+ ' Then '+@vTransFormCol +' Else Null End) As '+ 
        '['+Left(@vPivotCol,1)+@pCols+']'
--因长度限制,不计算列间之和
  Set @StrSum=@StrSum + '+IsNull(A.' + '['+Left(@vPivotCol,1)+@pCols+']' +',0)'
  End
  Set  @StrSql = ' Select ' + @vGroupByField + ' ' +@StrSql + ' From ' +@vSourceTAB+ ' ' + @StrWhere +
                 ' Group By ' + @vGroupByField 
  --列向合计 为字段名'TotalSum'
  Set @StrSql ='Select A.*,(0' +@StrSum + ') As TotalSum From (' + @StrSql  +') As A'
  Set @StrSql ='Select A.* From ('+@StrSql+') As A'
  Execute(@StrSql)
  Close CursorCross
  Deallocate CursorCross
End--CrossTable 'THCLD','storeid,dh','Qty','SUM','GoodsCode',''