if object_id('[tb]') is not null drop table [tb] go create table [tb]([Name] varchar(1),[Age] int,[NO] int,[Re] varchar(3)) insert [tb] select 'A',11,11,'ABC' union all select 'A',11,11,'CCC' union all select 'A',11,11,'AAA' union all select 'B',11,11,'CCC' union all select 'B',11,11,'DDD' union all select 'A',11,11,NULL union all select 'A',11,11,NULL goselect name,age,no, re=stuff((select ','+re from tb where re is not null and name=t.name and age=t.age and no=t.no for xml path('')),1,1,'') from tb t where re is not null group by name,age,no UNION SELECT * FROM TB WHERE REMARK IS NULL1楼的没有问题,我稍微改了下,不知道,在oracle里如何实现。
declare @tb_test table(id int identity(1,1),name varchar(50),age int,tno int,re varchar(50)) insert into @tb_test(name,age,tno,re) values('a',11,11,'abc') insert into @tb_test(name,age,tno,re) values('a',11,11,'ccc') insert into @tb_test(name,age,tno,re) values('a',11,11,'ddd') insert into @tb_test(name,age,tno,re) values('b',11,11,'ccc') insert into @tb_test(name,age,tno,re) values('b',11,11,'ddd') insert into @tb_test(name,age,tno,re) values('a',11,11,null)select name,age,tno, re=stuff((select ','+re from @tb_test where re is not null and name=t.name and age=t.age and tno=t.tno for xml path('')),1,1,'') from @tb_test t where re is not null group by name,age,tno union all select name,age,tno,re from @tb_test where re is null 我是学习上面的人又写了一份
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Name] varchar(1),[Age] int,[NO] int,[Re] varchar(3))
insert [tb]
select 'A',11,11,'ABC' union all
select 'A',11,11,'CCC' union all
select 'A',11,11,'AAA' union all
select 'B',11,11,'CCC' union all
select 'B',11,11,'DDD' union all
select 'A',11,11,NULL union all
select 'A',11,11,NULL
goselect name,age,no,
re=stuff((select ','+re from tb where re is not null and name=t.name and age=t.age and no=t.no for xml path('')),1,1,'')
from tb t
where re is not null
group by name,age,no
UNION
SELECT * FROM TB WHERE REMARK IS NULL1楼的没有问题,我稍微改了下,不知道,在oracle里如何实现。
insert into @tb_test(name,age,tno,re) values('a',11,11,'abc')
insert into @tb_test(name,age,tno,re) values('a',11,11,'ccc')
insert into @tb_test(name,age,tno,re) values('a',11,11,'ddd')
insert into @tb_test(name,age,tno,re) values('b',11,11,'ccc')
insert into @tb_test(name,age,tno,re) values('b',11,11,'ddd')
insert into @tb_test(name,age,tno,re) values('a',11,11,null)select name,age,tno,
re=stuff((select ','+re from @tb_test where re is not null and name=t.name and age=t.age and tno=t.tno for xml path('')),1,1,'')
from @tb_test t
where re is not null
group by name,age,tno
union all
select name,age,tno,re from @tb_test where re is null
我是学习上面的人又写了一份