select playerid,[itemname_1]=
max(case when itemname='100米' then itemname end),[itemname_2]=
max(case when itemname='200米' then itemname end),[itemname_3]=
max(case when itemname='400米' then itemname end),[itemname_4]=
max(case when itemname='铅球' then itemname end),[itemname_5]=
max(case when itemname='跳高' then itemname end) from table_item a inner join table_playerCheck b on a.itemid=b.itemid group by playerid
max(case when itemname='100米' then itemname end),[itemname_2]=
max(case when itemname='200米' then itemname end),[itemname_3]=
max(case when itemname='400米' then itemname end),[itemname_4]=
max(case when itemname='铅球' then itemname end),[itemname_5]=
max(case when itemname='跳高' then itemname end) from table_item a inner join table_playerCheck b on a.itemid=b.itemid group by playerid
(itemid int,itemname varchar(25))[create] table table_playerCheck
(playerid varchar(5),itemid int)insert table_item values (1,'100米')
insert table_item values (2,'200米')
insert table_item values (3,'400米')
insert table_item values (4,'跳高')
insert table_item values (5,'铅球')insert table_playerCheck values ('001',1)
insert table_playerCheck values ('001',3)
insert table_playerCheck values ('002',1)
insert table_playerCheck values ('002',2)
insert table_playerCheck values ('002',5)declare @sql varchar(8000),@i int
select @sql='select playerid',@i=1
select @sql=@sql+',[itemname_'+cast(@i as varchar)+']=
max(case when itemname='''+itemname+''' then itemname end)',@i=@i+1
from table_item
group by itemnameselect @sql=@sql+' from table_item a inner join table_playerCheck b on a.itemid=b.itemid group by playerid'exec(@sql)drop table table_item
drop table table_playerCheckplayerid itemname_1 itemname_2 itemname_3 itemname_4 itemname_5
-------- -------------------- -------------------- -------------------- -------------------- --------------------
001 100米 NULL 400米 NULL NULL
002 100米 200米 NULL 铅球 NULL
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 铅球
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,'铅球'
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',5select
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.itemiddeclare @i int,@s varchar(8000)
select @i=max(cnt),@s='' from #twhile @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
--------------------------------------------------------------------------
要在报表上显示:
21021 张三 100米 400米
21024 李四 100米 200米 铅球
--------------------------------------------------------------------------
所以我的临时表只有4个项目,到时候只要拖到水晶报表的页面就好了,如果不能实现这个临时表,只要得到我要的报表格式就好了。看看你有别的办法没?
也感谢其他两位热心的朋友!