参考:--测试数据 create table csdn(id int,txt varchar(10)) insert csdn select 1,'a' union all select 1,'b' union all select 1,'c' union all select 2,'aa' union all select 2,'bb' union all select 2,'cc' union all select 3,'aaa' union all select 3,'bbb' select * from csdn gocreate function Gettxt(@id int) returns varchar(8000) as begin declare @s varchar(8000) set @s='' select @s=@s +',' +txt from csdn where id=@id --return @s return stuff(@s,1,1,'') end goselect id,dbo.Gettxt(id) txt from csdn group by id godrop function Gettxt drop table csdn go
SELECT distinct(X),(select Y from try where left(y,1)=x) as B1,(select Y from try where left(y,1)=x and (y != B1)) as B2 FROM 表name没有调试 共搂住参考
if object_id('t')is not null drop table tcreate table t(id char(5),name char(5)) insert into t select 'A' , 'AA1' union all select 'A' , 'AA2' union all select 'B' , 'BB1' union all select 'B' , 'BB2'select * from ( select id, name1=(select top 1 name from t where id=s.id order by name asc), name2=(select top 1 name from t where id=s.id order by name desc) from t s)a group by id,name1,name2
create table csdn(id int,txt varchar(10))
insert csdn
select 1,'a' union all
select 1,'b' union all
select 1,'c' union all
select 2,'aa' union all
select 2,'bb' union all
select 2,'cc' union all
select 3,'aaa' union all
select 3,'bbb'
select * from csdn
gocreate function Gettxt(@id int)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=''
select @s=@s +',' +txt from csdn where id=@id
--return @s
return stuff(@s,1,1,'')
end
goselect id,dbo.Gettxt(id) txt from csdn group by id
godrop function Gettxt
drop table csdn
go
FROM 表name没有调试 共搂住参考
drop table tcreate table t(id char(5),name char(5))
insert into t
select
'A' , 'AA1'
union all select
'A' , 'AA2'
union all select
'B' , 'BB1'
union all select
'B' , 'BB2'select * from
(
select id,
name1=(select top 1 name from t where id=s.id order by name asc),
name2=(select top 1 name from t where id=s.id order by name desc)
from t s)a
group by id,name1,name2