create table mytest ( contract_id int, type_id int, fact_date varchar(8), fact_cash int )insert into mytest select 1,1,'20041012',5000 union select 1,23,'20041015',6000 union select 2,1,'20041012',8000 union select 2,23,'20041015',9000select distinct * from mytest a,mytest b where a.contract_id=b.contract_id and a.type_id<b.type_iddrop table mytest 不知道是不是您想要的
如果type_id不只是2种呢?比如说:type_id = 1,23,24,27,28怎么办呢?
create table ta (id int, value varchar(20)) Insert into ta select '1','aaa' union all select '2','bbb' union all select '3','ccc'create table tb (id int, value varchar(20)) Insert into tb select '1','aaa,bbb,' union all select '2','bbb,ccc,' union all select '3','aaa,bbb,ccc,'select * from ta select * from tb--函數 create function dbo.fn_m(@vchstring varchar(1000)) returns varchar(1000) as begin declare @intstart int,@intlocation int declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s='' select @intlocation = charindex(',',@vchstring,@intstart) while (@intlocation <>0 ) begin select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart) select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring select @intstart = @intlocation +1 select @intlocation = charindex(',',@vchstring,@intstart) end return(@s) end--刪除 drop table ta drop table tb drop function dbo.fn_mselect id, value=dbo.fn_m(value) from tb --結果 id value ------------------ 1 1,2, 2 2,3, 3 1,2,3, 这个例子不知道合你胃口不
create table mytest ( contract_id int, type_id int, fact_date varchar(8), fact_cash int )--测试 insert into mytest select 1,1,'20041012',5000 union select 1,23,'20041015',6000 union select 2,1,'20041012',8000 union select 2,23,'20041015',9000 declare @sql varchar(8000) set @sql='select contract_id' select @sql=@sql+',[type'+convert(varchar(20),type_id)+']=max('+convert(varchar(20),type_id)+ '),fact_date=max((case type_id when '+convert(varchar(20),type_id)+ ' then fact_date else '''' end)), fact_cash=max((case type_id when '+convert(varchar(20),type_id) +'then fact_cash else '''' end))' from mytest group by type_id print @sql exec(@sql+'from mytest group by contract_id') --结果 1 1 20041012 20041012 5000 23 20041015 20041015 6000 2 1 20041012 20041012 8000 23 20041015 20041015 9000
1 1 20041012 5000 23 20041015 6000
2 1 20041012 8000 23 20041015 9000
1 1 20041012 5000 23 20041015 6000
2 1 20041012 8000 23 20041015 9000
(
contract_id int,
type_id int,
fact_date varchar(8),
fact_cash int
)insert into mytest select 1,1,'20041012',5000
union select 1,23,'20041015',6000
union select 2,1,'20041012',8000
union select 2,23,'20041015',9000select distinct * from mytest a,mytest b where a.contract_id=b.contract_id and a.type_id<b.type_iddrop table mytest
不知道是不是您想要的
Insert into ta
select '1','aaa'
union all select '2','bbb'
union all select '3','ccc'create table tb (id int, value varchar(20))
Insert into tb
select '1','aaa,bbb,'
union all select '2','bbb,ccc,'
union all select '3','aaa,bbb,ccc,'select * from ta
select * from tb--函數
create function dbo.fn_m(@vchstring varchar(1000))
returns varchar(1000)
as
begin
declare @intstart int,@intlocation int
declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s=''
select @intlocation = charindex(',',@vchstring,@intstart)
while (@intlocation <>0 )
begin
select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart)
select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring
select @intstart = @intlocation +1
select @intlocation = charindex(',',@vchstring,@intstart)
end
return(@s)
end--刪除
drop table ta
drop table tb
drop function dbo.fn_mselect id, value=dbo.fn_m(value) from tb
--結果
id value
------------------
1 1,2,
2 2,3,
3 1,2,3,
这个例子不知道合你胃口不
create table mytest
(
contract_id int,
type_id int,
fact_date varchar(8),
fact_cash int
)--测试
insert into mytest select 1,1,'20041012',5000
union select 1,23,'20041015',6000
union select 2,1,'20041012',8000
union select 2,23,'20041015',9000
declare @sql varchar(8000)
set @sql='select contract_id'
select @sql=@sql+',[type'+convert(varchar(20),type_id)+']=max('+convert(varchar(20),type_id)+
'),fact_date=max((case type_id when '+convert(varchar(20),type_id)+
' then fact_date else '''' end)),
fact_cash=max((case type_id when '+convert(varchar(20),type_id) +'then fact_cash else '''' end))'
from mytest group by type_id
print @sql
exec(@sql+'from mytest group by contract_id')
--结果
1 1 20041012 20041012 5000 23 20041015 20041015 6000
2 1 20041012 20041012 8000 23 20041015 20041015 9000
contract_id type1 fact_date fact_cash type23 fact_date fact_cash
----------- ----------- --------- ----------- ----------- --------- -----------
1 1 20041012 5000 23 20041015 6000
2 1 20041012 8000 23 20041015 9000