create table C ( IDNO varchar(10), NAME1 varchar(10) ) create table B ( IDNO varchar(10), SEX varchar(10) ) insert C select '001','Xue' union all select '001','li' union all select '002','Wang' union all select '003','Zhang' insert B select '001','M' union select '002','W' union select '003','M'--测试 select [ID]=identity(int,1,1),* into ##tmp from C select *, [order]=(select count(1) from ##tmp where IDNO=t.IDNO and ID<=t.ID) into ## from ##tmp tdeclare @sql varchar(2000),@tmp int,@count int select @count=max([count]) from (select [count]=count(1) from C group by IDNO)t select @sql='',@tmp=1 while @tmp<=@count select @sql=@sql+',[Name0'+convert(varchar,@tmp)+']=max(case when [order]='+convert(varchar,@tmp)+' then Name1 else ''''end)', @tmp=@tmp+1set @sql='select t.IDNO,[Sex]=max(Sex)'+@sql+' from ## t left join B on t.IDNO=B.IDNO group by t.IDNO' --print @sql exec(@sql)
--删除测试环境 drop table C,B drop table ##tmp,##--结果 /* IDNO Sex Name01 Name02 ---------- ---------- ---------- ---------- 001 M Xue li 002 W Wang 003 M Zhang */
TO: vivianfdlpwselect t.IDNO,[Sex]=min(Sex), [Name01]=Max(case when [order]=1 then Name1 else ''end), [Name02]=Max(case when [order]=2 then Name1 else ''end) from ## t left join B on t.IDNO=B.IDNO group by t.IDNO为何Max(Name1)和MAX(Name2)我转换[Sex]=MAX(Sex) to min(Sex),好像也可以吗?谢谢,麻烦解释一下!!!!
(
IDNO varchar(10),
NAME1 varchar(10)
)
create table B
(
IDNO varchar(10),
SEX varchar(10)
)
insert C
select '001','Xue' union all
select '001','li' union all
select '002','Wang' union all
select '003','Zhang'
insert B
select '001','M' union
select '002','W' union
select '003','M'--测试
select [ID]=identity(int,1,1),* into ##tmp from C
select *,
[order]=(select count(1) from ##tmp where IDNO=t.IDNO and ID<=t.ID)
into ##
from ##tmp tdeclare @sql varchar(2000),@tmp int,@count int
select @count=max([count]) from (select [count]=count(1) from C group by IDNO)t
select @sql='',@tmp=1
while @tmp<=@count
select @sql=@sql+',[Name0'+convert(varchar,@tmp)+']=max(case when [order]='+convert(varchar,@tmp)+' then Name1 else ''''end)',
@tmp=@tmp+1set @sql='select t.IDNO,[Sex]=max(Sex)'+@sql+' from ## t left join B on t.IDNO=B.IDNO group by t.IDNO'
--print @sql
exec(@sql)
--删除测试环境
drop table C,B
drop table ##tmp,##--结果
/*
IDNO Sex Name01 Name02
---------- ---------- ---------- ----------
001 M Xue li
002 W Wang
003 M Zhang
*/
[Name01]=Max(case when [order]=1 then Name1
else ''end),
[Name02]=Max(case when [order]=2 then Name1 else ''end)
from ## t left join B
on t.IDNO=B.IDNO
group by t.IDNO为何Max(Name1)和MAX(Name2)我转换[Sex]=MAX(Sex) to min(Sex),好像也可以吗?谢谢,麻烦解释一下!!!!