接触 SQL 编程

做一个运动会的项目，要得到一个运动员所抱的项目，如下格式：

                                                                                计算机系代表队
                                    21001         张三         男             100米          200米         跳高
                                       ：               ：           ：                  ：               ：             ：

数据库这样的：Table_item,     Table_PlayerCheck ,     Table_Player

Table_item：                          Table_PlayerCheck：                       Table_Player
------------------------                  -------------------------------                   -----------------------------------------
itemid     itemname                    playerid       itemid                            playerid         name          sex
  1             100米                               001            1                                       001             张三           男
  2             200米                               001            3                                       002             李四           女
  3             400米                               002            1                                       003             王五           女
  4             跳高                                002            2                                        004             赵六           男
  5             铅球                                002            5                                        005             冯七           男

要得到一个临时表，做报表时用
----------------------------------------------------------------------------------
playerid   itemname_1   itemname_2  itemname_3  itemname_4
    001            100米             400米                null              null
    002            100米             200米               铅球             null
      :                   :                       :                       :                    :

开始想到用交叉连接：

CREATE TABLE Table_item(itemid int,itemname varchar(10))
CREATE TABLE Table_PlayerCheck(playerid  CHAR(3),itemid INT)

             
INSERT Table_item select 1,'100米'                   
INSERT Table_item select 2,'200米'                   
INSERT Table_item select 3,'400米'                   
INSERT Table_item select 4,'跳高'                   
INSERT Table_item select 5,'铅球'  
 
INSERT Table_PlayerCheck select '001',1                
INSERT Table_PlayerCheck select '001',3
INSERT Table_PlayerCheck select '002',1
INSERT Table_PlayerCheck select '002',2
INSERT Table_PlayerCheck select '002',5
 

declare @a varchar(8000)
set @a='select B.playerid'
SELECT  @a=@a+',['+'itemname_'+convert(varchar(10),itemid)+']=MAX(CASE A.itemid WHEN '''+convert(varchar(10),itemid)+''' THEN itemname else NULL  END)' FROM Table_item  GROUP BY itemid
EXEC(@a+' from Table_item A,Table_PlayerCheck B WHERE A.itemid=B.itemid group by B.playerid')

--结果
playerid    itemname_1   itemname_2   itemname_3    itemname_4   itemname_5
--------------------------------------------------------------------------------------------------------
    001            100米             NULL               400米               NULL            NULL
    002            100米             200米                NULL              NULL            铅球

发现不能达到效果，这样做的话，如果我有40个项目这个表头就得到 itemname_40 啊！做报表的格式也不正确啊！

最后实现的方法：

create table Table_item(itemid int,itemname varchar(20))
insert into Table_item select 1,'100米'
insert into Table_item select 2,'200米'
insert into Table_item select 3,'400米'
insert into Table_item select 4,'跳高'
insert into Table_item select 5,'铅球'
insert into Table_item select 6,'跳远'
insert into Table_item select 7,'1500米'

create table Table_PlayerCheck(playerid varchar(10),itemid int)
insert into Table_PlayerCheck select '001',1
insert into Table_PlayerCheck select '001',3
insert into Table_PlayerCheck select '002',1
insert into Table_PlayerCheck select '002',2
insert into Table_PlayerCheck select '002',5
insert into Table_PlayerCheck select '003',2
insert into Table_PlayerCheck select '003',4
insert into Table_PlayerCheck select '003',6
insert into Table_PlayerCheck select '003',7

select
    b.playerid,
    b.itemid,
    a.itemname,
    cnt = (select count(*) from Table_PlayerCheck where playerid=b.playerid and itemid<=b.itemid)
into #t
from
    Table_item a,
    Table_PlayerCheck b
where
    a.itemid=b.itemid

declare @i int,@s varchar(8000)
select @i=max(cnt),@s='' from #t

while @i>0
begin
    set @s=',itemname_'+rtrim(@i)+'=max(case cnt when '+rtrim(@i)+' then itemname end)'+@s
    set @i=@i-1
end
set @s='select playerid'+@s+' from #T group by playerid'

exec(@s)

drop table Table_item,Table_PlayerCheck,#t