create table tb_Test(cNo int, cType varchar(20), iVal int)
go
insert into tb_Test values(123, 'ab', 20)
insert into tb_Test values(123, 'cc', 12)
insert into tb_Test values(123, 'da', 30)
insert into tb_Test values(456, 'cc', 10)
insert into tb_Test values(456, 'ab', 20)
gocreate function dbo.f_str2(@i int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+cType+' '+convert(varchar(20),iVal)+' '
from tb_Test
where cNo=@i
return @s
endselect distinct cNo,dbo.f_str2(cNo)
from tb_Test
go
insert into tb_Test values(123, 'ab', 20)
insert into tb_Test values(123, 'cc', 12)
insert into tb_Test values(123, 'da', 30)
insert into tb_Test values(456, 'cc', 10)
insert into tb_Test values(456, 'ab', 20)
gocreate function dbo.f_str2(@i int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s+cType+' '+convert(varchar(20),iVal)+' '
from tb_Test
where cNo=@i
return @s
endselect distinct cNo,dbo.f_str2(cNo)
from tb_Test
insert into tb_Test values(123, 'ab', 20)
insert into tb_Test values(123, 'cc', 12)
insert into tb_Test values(123, 'da', 30)
insert into tb_Test values(456, 'cc', 10)
insert into tb_Test values(456, 'ab', 20)
goselect identity(int,1,1) as id,0 as nid,* into #t from tb_Test
update a
set
nid=(select count(*) from #t where cNo=a.cNo and id<=a.id)
from #t adeclare @s varchar(8000),@i int
select @s='',@i=max(nid) from #twhile @i>0
begin
set @s=',[cType'+rtrim(@i)+']=max(case nid when '+rtrim(@i)+' then cType end)'+
',[iVal'+rtrim(@i)+']=max(case nid when '+rtrim(@i)+' then iVal end)'+@s
set @i=@i-1
endset @s='select cNo'+@s+' from #t group by cNo'exec(@s)/*
cNo cType1 iVal1 cType2 iVal2 cType3 iVal3
----- -------- ------- -------- ------- -------- -------
123 ab 20 cc 12 da 30
456 cc 10 ab 20 NULL NULL
*/drop table #t,tb_Test
太菜 请教 为什么这里要加 ' '
===========这个100W是指这个表有100W条记录!
create table #t(cNo varchar(3),cType varchar(2),iVal int)
insert #t
select '123','ab',20 union all
select '123','cc',12 union all
select '123','da',30 union all
select '456','cc',10 union all
select '456','ab',20declare @i int,@s varchar(8000)
select id=identity(int,1,1),nid=0,* into #tt from #t
update t set t.nid=t.id-(select min(id) from #tt where cno=t.cno)+1 from #tt tselect @s='',@i=max(nid) from #tt
while @i>0
begin
select @s=',[ctype'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then ctype end),[ival'+cast(@i as varchar(10))+']=max(case when nid='+cast(@i as varchar(10))+' then ival end)'+@s,@i=@i-1
end
print @s
exec('select cno'+@s+ ' from #tt group by cno')/*
cno ctype1 ival1 ctype2 ival2 ctype3 ival3
---- ------ ----------- ------ ----------- ------ -----------
123 ab 20 cc 12 da 30
456 cc 10 ab 20 NULL NULL
*/
--------------------------------------------------------------------------
之前给出的查询语句应该足够用了.
123 aa 10
456 bb 20
tb_Temp
cNo cClass iVal cMonth
123 qq 30 200501
456 ww 40 200502要将两张表合成一张表,
根据 tb_Test 的 cNo 等于 tb_Temp 的 cNo,合成一行,即要得到如下结果:cNo cType iVal cClass iVal cMonth
123 aa 10 qq 30 200501
456 bb 20 ww 40 200502
a.*,b.cClass,b.iVal,b.cMonth
from
tb_Test a,tb_Temp b
where
a.cNo=b.cNo