--生成测试数据
create table t(No int,ID int,project varchar(20),way varchar(20))
insert into t select 1111,1,'方案1','广东'
insert into t select 1111,1,'方案2','广东'
insert into t select 1111,1,'方案3','广东'
insert into t select 2222,2,'方案a','湖南'
insert into t select 2222,2,'方案b','湖南'
--执行交叉表查询
declare @s varchar(8000),@i int
select identity(int,1,1) as NID,0 as ROWID,* into #t from t
select * from #tupdate a set ROWID = b.cnt
from
#t a,
(select c.NID,cnt=count(d.NID) from #t c,#t d where c.No=d.No and c.NID>=d.NID group by c.NID) b
where
a.NID = b.NIDselect @s = '',@i = max(a.cnt) from (select No,cnt = count(*) from #t group by No) awhile @i > 0
begin
set @s = ',project=max(case when ROWID='+rtrim(@i)+' then project end)'+@s
set @i = @i - 1
endset @s = 'select No,ID'+@s+',way from #t group by No,ID,way order by No'
exec(@s)--输出结果
No ID project project project way
---- ----- ------- ------- ------- ----
1111 1 方案1 方案2 方案3 广东
2222 2 方案a 方案b 湖南
create table t(No int,ID int,project varchar(20),way varchar(20))
insert into t select 1111,1,'方案1','广东'
insert into t select 1111,1,'方案2','广东'
insert into t select 1111,1,'方案3','广东'
insert into t select 2222,2,'方案a','湖南'
insert into t select 2222,2,'方案b','湖南'
--执行交叉表查询
declare @s varchar(8000),@i int
select identity(int,1,1) as NID,0 as ROWID,* into #t from t
select * from #tupdate a set ROWID = b.cnt
from
#t a,
(select c.NID,cnt=count(d.NID) from #t c,#t d where c.No=d.No and c.NID>=d.NID group by c.NID) b
where
a.NID = b.NIDselect @s = '',@i = max(a.cnt) from (select No,cnt = count(*) from #t group by No) awhile @i > 0
begin
set @s = ',project=max(case when ROWID='+rtrim(@i)+' then project end)'+@s
set @i = @i - 1
endset @s = 'select No,ID'+@s+',way from #t group by No,ID,way order by No'
exec(@s)--输出结果
No ID project project project way
---- ----- ------- ------- ------- ----
1111 1 方案1 方案2 方案3 广东
2222 2 方案a 方案b 湖南
create table tb
(
No varchar(10),
ID int,
project varchar(10),
way varchar(10)
)
insert tb
select '1111',1,'方案1','广东' union
select '1111',1,'方案2','广东' union
select '1111',1,'方案3','广东' union
select '2222',2,'方案1','湖南' union
select '2222',2,'方案b','湖南' if exists(select 1 from sysobjects where id=object_id('f_str') and xtype='FN')
drop function f_str
go
create function f_str(@ID int,@max int)
returns varchar(500)
as
begin
declare @str varchar(600),@tmp int
set @str=''
select @str=@str+''','''+project from tb where ID=@ID select @tmp=count(1) from tb where ID=@ID
set @str=@str+replicate(''','''+'',@max-@tmp)
return stuff(@str,1,3,'')
end
go--测试
declare @sql varchar(8000),@max int,@num int
select @sql='',@max=0
select @num=max([count]) from (select [count]=count(1) from tb group by No)t
set @max=@num
while @num>0
begin
set @sql=@sql+',project'+convert(varchar,@num)+' varchar(10)'
set @num=@num-1
end
set @sql=' create table ##(No varchar(10),ID int'+@sql+',way varchar(10))'
exec(@sql)declare cur cursor for select No,ID,way from tb group by No,ID,way
open cur
declare @No varchar(10),@ID int,@way varchar(10),@tmp varchar(5000)
fetch next from cur into @No,@ID,@way
print @@fetch_status
while @@fetch_status=0
begin
set @tmp='select '''+@No+''','+convert(varchar,@ID)+','''+dbo.f_str(@ID,@max)+''','''+@way+''''
--print @tmp
insert ## exec(@tmp)
fetch next from cur into @No,@ID,@way
end
close cur
deallocate cur
select * from ##
drop table ##--删除测试环境
drop table tb--结果
/*
No ID project3 project2 project1 way
---------- ----------- ---------- ---------- ---------- ----------
1111 1 方案1 方案2 方案3 广东
2222 2 方案1 方案b 湖南(所影响的行数为 2 行)
*/
--------------------------------------------------------------------------------
--生成80,000条测试数据
create table t(No int,ID int,project varchar(20),way varchar(20))declare @i int,@j int
set @i = 1111
set @j = 1
while @j <= 10000
begin
insert into t select @i,@j,'方案1','广东'
insert into t select @i,@j,'方案2','广东'
insert into t select @i,@j,'方案3','广东'
insert into t select @i,@j,'方案a','广东'
insert into t select @i,@j,'方案b','广东'
insert into t select @i,@j,'方案c','广东'
insert into t select @i,@j,'方案d','广东'
insert into t select @i,@j,'方案e','广东'
set @i=@i+1
set @j=@j+1
end
--执行查询
declare @s varchar(8000),@i int
select identity(int,1,1) as NID,0 as ROWID,* into #t from t
select * from #tupdate a set ROWID = b.cnt
from
#t a,
(select c.NID,cnt=count(d.NID) from #t c,#t d where c.No=d.No and c.NID>=d.NID group by c.NID) b
where
a.NID = b.NIDselect @s = '',@i = max(a.cnt) from (select No,cnt = count(*) from #t group by No) awhile @i > 0
begin
set @s = ',project=max(case when ROWID='+rtrim(@i)+' then project end)'+@s
set @i = @i - 1
endset @s = 'select No,ID'+@s+',way from #t group by No,ID,way order by No'print @sexec(@s)
Create table t (id varchar(5),公司 varchar(6),职员 varchar(4),金额 money)
insert into t select 'F0001','A公司','王三',100
union all select 'F0001','A公司','李杰',200
union all select 'F0001','B公司','张静',300
union all select 'F0002','A公司','李五',150
union all select 'F0002','C公司','赵为',350
--函数
Create function F_TGetStr(@Item as varchar(10),@bit bit)
returns varchar(200)
as
begin
declare @s as varchar(200)
set @s=''
if @bit=0
begin
select @s=@s+','+ltrim(rtrim(公司)) from
(select distinct 公司 from T where id=@item)
A
end
if @bit=1
begin
select @s=@s+','+ltrim(rtrim(职员)) from
(select distinct 职员 from T where id=@item)
A
end
return stuff(@s,1,1,'')
end--查询
select id,公司=dbo.F_TGetStr(id,0),职员=dbo.F_TGetStr(ID,1),金额=sum(金额)
from t group by ID
将上面的陶一下就可以了